• From: Eskilstuna, Sweden
  • Registered: 2004-05-23
  • Posts: 143

Topic: extremely slow search query

mysql> SELECT m.post_id FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgadgag%';
Empty set (39.52 sec)

mysql> explain SELECT m.post_id FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgadgag%';
+----+-------------+-------+------+----------------------------+---------------------+---------+-----------------+---------+-------------+
| id | select_type | table | type | possible_keys              | key                 | key_len | ref             | rows    | Extra       |
+----+-------------+-------+------+----------------------------+---------------------+---------+-----------------+---------+-------------+
|  1 | SIMPLE      | m     | ALL  | search_matches_word_id_idx | NULL                | NULL    | NULL            | 2190300 |             |
|  1 | SIMPLE      | w     | ref  | search_words_id_idx        | search_words_id_idx | 3       | forum.m.word_id |       1 | Using where |
+----+-------------+-------+------+----------------------------+---------------------+---------+-----------------+---------+-------------+

Its the same with only one % ... should it be this slow? The server is fast, with a lot of memory and a proper my.cnf (well, it should be anyway).

chrizz's Website

2 Reply by chrizz (edited by chrizz 2006-05-18 16:58)

  • From: Eskilstuna, Sweden
  • Registered: 2004-05-23
  • Posts: 143

Re: extremely slow search query

mysql> SELECT m.post_id FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgaadgag%';
Empty set (0.20 sec)

mysql> explain SELECT m.post_id FROM search_words AS w INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgaadgag%';
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref        | rows   | Extra       |
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
| 1  | SIMPLE      | w     | ALL  | search_words_id_idx        |                            |         |            | 133758 | Using where |
| 1  | SIMPLE      | m     | ref  | search_matches_word_id_idx | search_matches_word_id_idx | 3       | forum.w.id | 12     |             |
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
2 rows in set (0.01 sec)

This is run on a old dev. server im using, only about 30 000 less posts in that database. Here it's fast... how come?

Notice and compare the number of examined rows in the both examples..

edit: ah, different indexes are used here... I'll look into that.

chrizz's Website

  • From: Eskilstuna, Sweden
  • Registered: 2004-05-23
  • Posts: 143

Re: extremely slow search query

mysql> SELECT m.post_id FROM search_words AS w IGNORE INDEX (search_words_id_idx) INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgadgag%';
Empty set (0.06 sec)

mysql> explain SELECT m.post_id FROM search_words AS w IGNORE INDEX (search_words_id_idx) INNER JOIN search_matches AS m ON m.word_id = w.id WHERE w.word LIKE '%vadgadgag%';
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
| id | select_type | table | type | possible_keys              | key                        | key_len | ref        | rows   | Extra       |
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
|  1 | SIMPLE      | w     | ALL  | NULL                       | NULL                       | NULL    | NULL       | 159117 | Using where |
|  1 | SIMPLE      | m     | ref  | search_matches_word_id_idx | search_matches_word_id_idx | 3       | forum.w.id |     14 |             |
+----+-------------+-------+------+----------------------------+----------------------------+---------+------------+--------+-------------+
2 rows in set (0.01 sec)

This, with some modification in the query to ignore a index is fast (on the fast server)...

I have not examined how the search function in punbb actually works, but this does it for that type of search query anyway...

Rickard? Any explenation to this, and is it "safe" to enter this in the code to ignore this index?

chrizz's Website

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: extremely slow search query

That's odd. It appears MySQL joins the tables in a different order. I will look into this for 1.3. Thanks for the debug info smile

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website