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
---------------------------------------------------------------------
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
