Hi! >>>>> "Artem" == Artem Koutchine <[EMAIL PROTECTED]> writes: Artem> Hi! Artem> I have notice a very weird index selection when execution a query Artem> in 3.23.36. Artem> Here is an example: Artem> The table is: <cut> Artem> Now i want to find a product which contains both 'AMD' and 'DURON'. I Artem> do a select where I join Artem> the table with itself. mysql> explain SELECT -> w0.product_id -> FROM -> words AS w0 LEFT JOIN words AS w1 ON w1.product_id = Artem> w0.product_id AND w1.sys_del = 0 -> WHERE -> w0.sys_del=0 AND (w0.word_i LIKE 'amd%' AND w1.word_i Artem> LIKE 'duron%') and w1.product_id=w0.product_id -> ; Artem> +-------+-------+----------------------------------------------------- Artem> ----------------------------------+-----------------+---------+------- Artem> +------+------------+ Artem> | table | type | possible_keys Artem> | key | key_len | ref | rows | Extra | Artem> +-------+-------+----------------------------------------------------- Artem> ----------------------------------+-----------------+---------+------- Artem> +------+------------+ Artem> | w0 | range | Artem> i_words_word_i,i_words_product_id,i_words_www,i_words_eee,i_words_rrr, Artem> i_words_sys_del | i_words_eee | 51 | NULL | 25 | where Artem> used | Artem> | w1 | ref | Artem> i_words_product_id,i_words_www,i_words_rrr,i_words_sys_del Artem> | i_words_sys_del | 1 | const | 10 | where used | Artem> +-------+-------+----------------------------------------------------- Artem> ----------------------------------+-----------------+---------+------- Artem> +------+------------+ Artem> 2 rows in set (0.01 sec) Artem> As you see it uses two indexes: i_word_eee for keyword match and Artem> i_word_sys_del for the join. However Artem> it is clear, that using i_words_www or i_words_rrr is a lot better. Artem> Executing the query above takes Artem> 8-9 seconds. Artem> Now drop the index which mysql wants to use mysql> alter table words drop index i_words_sys_del; <cut> Artem> +-------+-------+----------------------------------------------------- Artem> ------------------+--------------------+---------+---------------+---- Artem> --+------------+ Artem> | table | type | possible_keys Artem> | key | key_len | ref | rows | Extra | Artem> +-------+-------+----------------------------------------------------- Artem> ------------------+--------------------+---------+---------------+---- Artem> --+------------+ Artem> | w0 | range | Artem> i_words_word_i,i_words_product_id,i_words_www,i_words_eee,i_words_rrr Artem> | i_words_eee | 51 | NULL | 25 | where used | Artem> | w1 | ref | i_words_product_id,i_words_www,i_words_rrr Artem> | i_words_product_id | 4 | w0.product_id | 11 | where used | Artem> +-------+-------+----------------------------------------------------- Artem> ------------------+--------------------+---------+---------------+---- Artem> --+------------+ Artem> 2 rows in set (0.01 sec) Artem> AhA! Now it uses i_word_rrr ! <cut> Artem> So, why Mysql chooses the wrong index? If you look close at the explain, you will see the reason: When using 'i_words_sys_del' MySQL guesses that there will be about 10 matching rows for each row in w0. When using 'i_words_product_id' MySQL guesses that there will be about 11 matching rows for each row in w0. Because of this MySQL desides to use the first index. The reason is that MySQL by default hasn't enough information about the key distribution to be able to always knows which index is the best to use and it has to do an 'educated guess' in cases where you are comparing keys with something that isn't a constant. Simple fix: Run optimize table on the words table and try again. Regards, Monty --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
