1 Topic by chrizz (edited by chrizz 2006-08-30 10:52)

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

Topic: Very slow search (query)

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 '%text%';
+----+-------------+-------+------+----------------------------+---------------------+---------+-----------------+---------+-------------+
| 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            | 3268634 |             |
|  1 | SIMPLE      | w     | ref  | search_words_id_idx        | search_words_id_idx | 3       | forum.m.word_id |       1 | Using where |
+----+-------------+-------+------+----------------------------+---------------------+---------+-----------------+---------+-------------+

This query takes about 30-40 seconds to run.

What could be wrong here?




other example, witch works fine: 

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 'text';
+----+-------------+-------+-------+-----------------------------+----------------------------+---------+------------+------+-------------+
| id | select_type | table | type  | possible_keys               | key                        | key_len | ref        | rows | Extra       |
+----+-------------+-------+-------+-----------------------------+----------------------------+---------+------------+------+-------------+
|  1 | SIMPLE      | w     | range | PRIMARY,search_words_id_idx | PRIMARY                    | 22      | NULL       |    1 | Using where |
|  1 | SIMPLE      | m     | ref   | search_matches_word_id_idx  | search_matches_word_id_idx | 3       | forum.w.id |   17 |             |
+----+-------------+-------+-------+-----------------------------+----------------------------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

Notice the difference between the two "... LIKE '%text%'" and "... LIKE 'text'"

chrizz's Website

  • From: Leuven, Belgium
  • Registered: 2005-06-14
  • Posts: 2,653

Re: Very slow search (query)

Well, %text% is a wildcard match. It matches everything with 'text' in it wink

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

Re: Very slow search (query)

elbekko wrote:

Well, %text% is a wildcard match. It matches everything with 'text' in it wink

I know, but why is not index in search_matches being used suddenly?

chrizz's Website

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

Re: Very slow search (query)

I cant specify what index to be used either. It refuses to use the ones I ask for...

It gets better, but not good:

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

chrizz's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Very slow search (query)

Err, the index can't help here for a very simple reason: you're not searching for an ID, you're searching for a word.

6 Reply by chrizz (edited by chrizz 2006-08-31 09:38)

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

Re: Very slow search (query)

Smartys wrote:

Err, the index can't help here for a very simple reason: you're not searching for an ID, you're searching for a word.

Then this is an bad solution. The query is straight from punbb... and is slow as hell. I guess it should not be this slow ...

I'm really hoping for mysql fulltext search in new versions... or I'll have to convert punbb myself... this does not work well at all hmm

chrizz's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Very slow search (query)

PunRes has a fulltext search mod

  • Registered: 2006-06-29
  • Posts: 324

Re: Very slow search (query)

compare it w/ pulling every post in the forum and matching it out in php...  I'm sure the %text% will be faster.

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

Re: Very slow search (query)

chrizz: There's no faster way to search a large table of words (that I know of) unless we switch to MySQL's built-in fulltext indexing. The reason %text% is a lot slower than text% is that the latter assumes the word must start with text. If we search for %text%, MySQL has to read every byte of every word (or at least until the search pattern can't fit).

What you could do is strip out some more noise words from your index. That might speed up the query a bit. Run the following query:

SELECT sw.word, COUNT(sm.post_id) AS hits
FROM search_words AS sw
INNER JOIN search_matches AS sm ON sw.id = sm.word_id
GROUP BY sw.id
ORDER BY hits DESC LIMIT 50

That'll list the top 50 most frequent words used in your forum. I'm sure at least 20 of those words can be added to stopwords.txt in your language pack. Then just rebuild your search index.

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

Rickard's Website

  • Registered: 2006-06-29
  • Posts: 324

Re: Very slow search (query)

think 30-40 seconds is slow? 

we do a massive tracking system for united way that has some fairly complex searching of the database (sproc's that are hundreds of lines long).  we moved the searches off of the sqlserver and into an online search engines, and the query that indexes the search data into the search engine takes about 5 hours to execute, and thats a seriously optimized query too.

  • From: U.S.A.
  • Registered: 2005-08-26
  • Posts: 1,607

Re: Very slow search (query)

MadHatter wrote:

think 30-40 seconds is slow? 

we do a massive tracking system for united way that has some fairly complex searching of the database (sproc's that are hundreds of lines long).  we moved the searches off of the sqlserver and into an online search engines, and the query that indexes the search data into the search engine takes about 5 hours to execute, and thats a seriously optimized query too.

Ouch. Sounds like fun. sad

Looking for a certain modification for your forum? Please take a look here before posting.
  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Very slow search (query)

Even though 30-40 seconds might not be a lot in the greater scheme of things, it's incredibly annoying from a user's POV.

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

Rickard's Website

13 Reply by chrizz (edited by chrizz 2006-09-02 08:08)

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

Re: Very slow search (query)

Rickard wrote:

Even though 30-40 seconds might not be a lot in the greater scheme of things, it's incredibly annoying from a user's POV.

Not only that, it's an security issue that consumes lot of server power and slows down the entire database and browsing for all users...

chrizz's Website

  • Registered: 2006-06-29
  • Posts: 324

Re: Very slow search (query)

lol, how is it a security issue, and why would one query (or even a whole bunch) slow the database performance down for the rest of the users.  databases are highly optimized applications which are specifically designed and written to 1) have a lot of connections, and 2) look for data.

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: Very slow search (query)

I wouldn't call it a security issue wink
And as MadHatter said, that query shouldn't lock any of the tables.

16 Reply by chrizz (edited by chrizz 2006-09-03 07:10)

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

Re: Very slow search (query)

MadHatter wrote:

lol, how is it a security issue, and why would one query (or even a whole bunch) slow the database performance down for the rest of the users.  databases are highly optimized applications which are specifically designed and written to 1) have a lot of connections, and 2) look for data.

LOL at yourself. Before you have all the facts an experience of running a high traffic site (wich I actually dont know if you have, but if you did you would probably not react the way you did) you should shut up.

1) A query that takes 50 seconds to run takes a lot of cpu.
2) if you only have 1 server for database and webb all is affected when this query eats cpu.
3) say 10 searches are made, and cpu load gets to 100% for at least 5-10 minutes.

This slows down everything like hell before all queries are done...

So please, dont say anything else before you actually KNOW it... I know this for a fact, else I would have not taken this up to be an issue at all.

Edit: "Security issue" may be the wrong words, but i'ts certanely an issue!

chrizz's Website

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

Re: Very slow search (query)

Settle down now. I'm quite aware of the issue. There's not a lot I can do about it though apart from implementing support for MySQL fulltext indexing (something that is on the todo list for 1.3).

chrizz: In the meantime, maybe you should hack your search.php to strip out any leading %?

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

Rickard's Website