| Bug #17640 | wrong where clause optimization | ||
|---|---|---|---|
| Submitted: | 22 Feb 2006 10:18 | Modified: | 13 Nov 2007 17:17 |
| Reporter: | Andre Timmer | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.0.18 | OS: | Any (All) |
| Assigned to: | CPU Architecture: | Any | |
[22 Feb 2006 10:48]
Valeriy Kravchuk
Thank you for a problem report. Can you, please, send a dump of tables with data to show the behaviour you described? SHOW CREATE TABLE and SHOW TABLE STATUS results for all the tables involved are needed anyway. Have you ANALIZEd the tables in question? If optimizer select obviously incorrect order of execution, and we will prove that, it will be a bug to fix. If you just want to ask about the ability to add hints for optimizer, add a separate feature request for that.
[22 Feb 2006 12:21]
Andre Timmer
Sorry, the optimizer used the right exists condition first. It seems to read the where clause from top to bottom :-). I was confused because the explain plan is not ordered on id. Maybe you can change the order of the rows in the explain plan. +----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+ | 1 | PRIMARY | aa | ALL | PRIMARY | NULL | NULL | NULL | 6 | | | 1 | PRIMARY | bb | ref | functie_i1 | functie_i1 | 4 | bbr.aa.bkwi_persoonsnr_marktselect | 1 | Using where; Using index | | 1 | PRIMARY | cc | eq_ref | PRIMARY | PRIMARY | 2 | bbr.bb.code_functie | 1 | | | 3 | DEPENDENT SUBQUERY | xx | eq_ref | PRIMARY | PRIMARY | 6 | bbr.bb.bkwi_id,bbr.bb.bkwi_volgnr | 1 | Using where | | 2 | DEPENDENT SUBQUERY | xx | ref | adres_uk,adres_ref_gemeente_fk | adres_uk | 6 | bbr.bb.bkwi_id,bbr.bb.bkwi_volgnr | 1 | Using where | +----+--------------------+-------+--------+--------------------------------+------------+---------+------------------------------------+------+--------------------------+
[23 Feb 2006 10:05]
Valeriy Kravchuk
So, we can call this a (reasonable) feature request: EXPLAIN should output results ordered by id. Maybe, manual (http://dev.mysql.com/doc/refman/5.0/en/explain.html) should be also corrected, because now it just says: "The tables are listed in the output in the order that MySQL would read them while processing the query." Do you agree with me?
[23 Feb 2006 10:08]
Andre Timmer
I do, thanks.
Description: The order of where clause execution cannot be overridden. There is no hint for to influence behaviour. How to repeat: select bb.bkwi_id , bb.bkwi_volgnr , bb.bkwi_persoonsnr_marktselect , aa.achternaam , aa.titels_achter , bb.code_functie , cc.omschrijving from contactpersoon aa join functie bb on (bb.bkwi_persoonsnr_marktselect = aa.bkwi_persoonsnr_marktselect) left join ref_functie cc on (cc.code = bb.code_functie) where exists ( select '' from adres xx where xx.bkwi_id = bb.bkwi_id and xx.bkwi_volgnr = bb.bkwi_volgnr and xx.code_gemeente in (0307, 0313, 0327, 0351) ) and exists ( select '' from vestiging xx where xx.bkwi_id = bb.bkwi_id and xx.bkwi_volgnr = bb.bkwi_volgnr and xx.ind_economisch_actief = 1 ); In the explain plan the second exists is evaluated first. The optimizer makes this decision, i think, because: - the first exists joins on primary key - the second exists joins only on first 2 columns of 5 column primary key The problem with this is that the first exists in this query is highly selective and the second isn't. Suggested fix: A hint to influence behaviour. For example: select .. from .. where use order ..