Bug #18688 non-optimal plan for SELECT
Submitted: 31 Mar 2006 14:07 Modified: 7 Sep 2007 16:53
Reporter: Victoria Reznichenko Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.0, 4.1, 5.0 OS:Linux (linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[31 Mar 2006 14:07] Victoria Reznichenko
Description:
EXPLAIN provides non-optimal plan for SELECT query, if I rewrite the same query with STRAIGHT_JOIN query becomes more than 30 times faster!

This is non-optimal plan for query:

mysql> EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w, new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;
+----+-------------+-------+------+---------------+---------+---------+----------------+---------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref            | rows    | Extra       |
+----+-------------+-------+------+---------------+---------+---------+----------------+---------+-------------+
|  1 | SIMPLE      | m     | ALL  | word_id       | NULL    | NULL    | NULL           | 1000000 | Using where |
|  1 | SIMPLE      | w     | ref  | word_id       | word_id | 3       | test.m.word_id |       1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+----------------+---------+-------------+
2 rows in set (0.01 sec)

This is EXPLAIN for query with STRAIGHT_JOIN:

mysql> EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w straight_join new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;
+----+-------------+-------+------+---------------+---------+---------+----------------+--------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref            | rows   | Extra       |
+----+-------------+-------+------+---------------+---------+---------+----------------+--------+-------------+
|  1 | SIMPLE      | w     | ALL  | word_id       | NULL    |    NULL | NULL           | 255919 | Using where |
|  1 | SIMPLE      | m     | ref  | word_id       | word_id |       3 | test.w.word_id |     14 | Using where |
+----+-------------+-------+------+---------------+---------+---------+----------------+--------+-------------+
2 rows in set (0.00 sec)

Initialy this problem was reported for 4.0 version and EXPLAIN output was changed after REPAIR .. USE_FRM though table was not corrupted and both REPAIR and REPAIR .. USE_FRM reports Ok.

This is output for 4.0 version of MySQL:

mysql> EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w, new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;
+-------+------+---------------+---------+---------+-----------+---------+-------------+
| table | type | possible_keys | key     | key_len | ref       | rows    | Extra       |
+-------+------+---------------+---------+---------+-----------+---------+-------------+
| m     | ALL  | word_id       | NULL    |    NULL | NULL      | 1000000 | Using where |
| w     | ref  | word_id       | word_id |       3 | m.word_id |       1 | Using where |
+-------+------+---------------+---------+---------+-----------+---------+-------------+
2 rows in set (0.02 sec)

mysql> repair table phpbb2_search_wordlist use_frm;
+-----------------------------+--------+----------+-----------------------------------------+
| Table                       | Op     | Msg_type | Msg_text                                |
+-----------------------------+--------+----------+-----------------------------------------+
| test.phpbb2_search_wordlist | repair | warning  | Number of rows changed from 0 to 255919 |
| test.phpbb2_search_wordlist | repair | status   | OK                                      |
+-----------------------------+--------+----------+-----------------------------------------+
2 rows in set (30.96 sec)

mysql> EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w, new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;
+-------+------+---------------+---------+---------+-----------+--------+-------------+
| table | type | possible_keys | key     | key_len | ref       | rows   | Extra       |
+-------+------+---------------+---------+---------+-----------+--------+-------------+
| w     | ALL  | word_id       | NULL    |    NULL | NULL      | 255919 | Using where |
| m     | ref  | word_id       | word_id |       3 | w.word_id |     14 | Using where |
+-------+------+---------------+---------+---------+-----------+--------+-------------+
2 rows in set (0.00 sec)

How to repeat:
1. restore tables.
2. Run the following EXPLAINs and SELECTs and compare the results:

EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w, new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;

EXPLAIN SELECT m.post_id FROM phpbb2_search_wordlist w straight_join new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;

SELECT m.post_id FROM phpbb2_search_wordlist w, new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;

SELECT m.post_id FROM phpbb2_search_wordlist w straight_join new_phpbb2_search_wordmatch m WHERE w.word_text LIKE '%something%' AND m.word_id = w.word_id AND w.word_common <> 1 AND m.title_match = 0;
[17 Aug 2007 21:16] Igor Babaev
This is not an optimizer bug. A skewed tree is built after repair.  An estimate of the number of records in a range for a skewed tree may be awful. This is the case.  

I move the bug to 'To be fixed later'. 
Product management will decide in what version a fix for this problem appears.
[27 Sep 2008 12:13] Konstantin Osipov
Setting the category back to 'Optimizer' to be able to see clearly the amount of 'To be fixed later' bugs that in one way or the other affect the optimizer.