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;
