Artem Koutchine writes:
> Hi!
>
> I have notice a very weird index selection when execution a query
> in 3.23.36.
>
>
> Here is an example:
>
> The table is:
>
> mysql> describe words;
> +------------+----------------------+------+-----+--------------------
> -+-------+
> | Field | Type | Null | Key | Default
> | Extra |
> +------------+----------------------+------+-----+--------------------
> -+-------+
> | id | int(10) unsigned | | PRI | 0
> | |
> | word_i | char(50) | | MUL |
> | |
> | product_id | int(10) unsigned | | MUL | 0
> | |
> | position | smallint(5) unsigned | | MUL | 0
> | |
> | sys_mdate | datetime | | | 0000-00-00 00:00:00
> | |
> | sys_cdate | datetime | | | 0000-00-00 00:00:00
> | |
> | sys_csite | char(10) | | |
> | |
> | sys_msite | char(10) | | MUL |
> | |
> | sys_cp | int(11) | | | 0
> | |
> | sys_mp | int(11) | | | 0
> | |
> | sys_del | tinyint(4) | | MUL | 0
> | |
> +------------+----------------------+------+-----+--------------------
> -+-------+
> 11 rows in set (0.00 sec)
>
> It is used to index descriptions and names and other text data for
> products and they quickly search for a product by specified keyword.
>
> The indexes are:
>
> +-------+------------+--------------------+--------------+------------
> -+-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name
> | Collation | Cardinality | Sub_part | Packed | Comment |
> +-------+------------+--------------------+--------------+------------
> -+-----------+-------------+----------+--------+---------+
> | words | 0 | PRIMARY | 1 | id
> | A | 17848 | NULL | NULL | |
> | words | 1 | i_words_word_i | 1 | word_i
> | A | 4462 | NULL | NULL | |
> | words | 1 | i_words_product_id | 1 | product_id
> | A | 1622 | NULL | NULL | |
> | words | 1 | i_words_position | 1 | position
> | A | 64 | NULL | NULL | |
> | words | 1 | i_words_sysupd | 1 | sys_msite
> | A | 1 | NULL | NULL | |
> | words | 1 | i_words_sysupd | 2 | sys_mdate
> | A | 189 | NULL | NULL | |
> | words | 1 | i_words_qqq | 1 | id
> | A | 17848 | NULL | NULL | |
> | words | 1 | i_words_qqq | 2 | word_i
> | A | 17848 | NULL | NULL | |
> | words | 1 | i_words_qqq | 3 | product_id
> | A | 17848 | NULL | NULL | |
> | words | 1 | i_words_qqq | 4 | sys_del
> | A | 17848 | NULL | NULL | |
> | words | 1 | i_words_www | 1 | product_id
> | A | 1622 | NULL | NULL | |
> | words | 1 | i_words_www | 2 | sys_del
> | A | 1622 | NULL | NULL | |
> | words | 1 | i_words_eee | 1 | word_i
> | A | 4462 | NULL | NULL | |
> | words | 1 | i_words_eee | 2 | sys_del
> | A | 4462 | NULL | NULL | |
> | words | 1 | i_words_rrr | 1 | sys_del
> | A | 1 | NULL | NULL | |
> | words | 1 | i_words_rrr | 2 | product_id
> | A | 1622 | NULL | NULL | |
> | words | 1 | i_words_sys_del | 1 | sys_del
> | A | 1 | NULL | NULL | |
> +-------+------------+--------------------+--------------+------------
> -+-----------+-------------+----------+--------+---------+
> 17 rows in set (0.00 sec)
>
> It looks like overkill but this is only for the testing perpose, so i
> can see why MySQL will choose.
>
>
> Now i want to find a product which contains both 'AMD' and 'DURON'. I
> do a select where I join
> the table with itself.
>
> mysql> explain SELECT
> -> w0.product_id
> -> FROM
> -> words AS w0 LEFT JOIN words AS w1 ON w1.product_id =
> w0.product_id AND w1.sys_del = 0
> -> WHERE
> -> w0.sys_del=0 AND (w0.word_i LIKE 'amd%' AND w1.word_i
> LIKE 'duron%') and w1.product_id=w0.product_id
> -> ;
> +-------+-------+-----------------------------------------------------
> ----------------------------------+-----------------+---------+-------
> +------+------------+
> | table | type | possible_keys
> | key | key_len | ref | rows | Extra |
> +-------+-------+-----------------------------------------------------
> ----------------------------------+-----------------+---------+-------
> +------+------------+
> | w0 | range |
> i_words_word_i,i_words_product_id,i_words_www,i_words_eee,i_words_rrr,
> i_words_sys_del | i_words_eee | 51 | NULL | 25 | where
> used |
> | w1 | ref |
> i_words_product_id,i_words_www,i_words_rrr,i_words_sys_del
> | i_words_sys_del | 1 | const | 10 | where used |
> +-------+-------+-----------------------------------------------------
> ----------------------------------+-----------------+---------+-------
> +------+------------+
> 2 rows in set (0.01 sec)
>
> As you see it uses two indexes: i_word_eee for keyword match and
> i_word_sys_del for the join. However
> it is clear, that using i_words_www or i_words_rrr is a lot better.
> Executing the query above takes
> 8-9 seconds.
>
>
> Now drop the index which mysql wants to use
>
> mysql> alter table words drop index i_words_sys_del;
>
> mysql> explain SELECT
> -> w0.product_id
> -> FROM
> -> words AS w0 LEFT JOIN words AS w1 ON w1.product_id =
> w0.product_id AND w1.sys_del = 0
> -> WHERE
> -> w0.sys_del=0 AND (w0.word_i LIKE 'amd%' AND w1.word_i
> LIKE 'duron%') and w1.product_id=w0.product_id
> -> ;
> +-------+-------+-----------------------------------------------------
> ------------------+--------------------+---------+---------------+----
> --+------------+
> | table | type | possible_keys
> | key | key_len | ref | rows | Extra |
> +-------+-------+-----------------------------------------------------
> ------------------+--------------------+---------+---------------+----
> --+------------+
> | w0 | range |
> i_words_word_i,i_words_product_id,i_words_www,i_words_eee,i_words_rrr
> | i_words_eee | 51 | NULL | 25 | where used |
> | w1 | ref | i_words_product_id,i_words_www,i_words_rrr
> | i_words_product_id | 4 | w0.product_id | 11 | where used |
> +-------+-------+-----------------------------------------------------
> ------------------+--------------------+---------+---------------+----
> --+------------+
> 2 rows in set (0.01 sec)
>
> AhA! Now it uses i_word_rrr !
>
> mysql> SELECT
> -> w0.product_id
> -> FROM
> -> words AS w0 LEFT JOIN words AS w1 ON w1.product_id =
> w0.product_id AND w1.sys_del = 0
> -> WHERE
> -> w0.sys_del=0 AND (w0.word_i LIKE 'amd%' AND w1.word_i
> LIKE 'duron%') and w1.product_id=w0.product_id
> -> ;
> +------------+
> | product_id |
> +------------+
> | 202101 |
> | 202201 |
> | 234201 |
> | 234201 |
> | 201801 |
> | 201901 |
> | 202001 |
> | 234101 |
> | 234201 |
> | 234201 |
> +------------+
> 10 rows in set (0.02 sec)
>
>
> Voila!! 0.02 seconds agains 8 seconds.
>
> So, why Mysql chooses the wrong index?
>
> Regards,
> Artem
>
>
Hi!
Thanks for the report.
We shall examine the matter.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
