How can I speed up this query?

[Shpigford]

I've got a table with just over 1 million rows and am trying to do the following fulltext search on it:

SELECT `id`, `parent` 
FROM `comments` 
WHERE MATCH(title, body) AGAINST('*search term*' IN BOOLEAN MODE) 
  AND `type` = 'forum' 
  AND `active` = 'Y' 
  AND `item_id` != 45

Here is the EXPLAIN output:

id  select_type   table	    type   possible_keys   key   key_len	ref	rows      Extra  
1    SIMPLE	   comments	fulltext	commentindex,active_reported,title	title	    0	   1   Using where

It's not using any sort of filesort or temporary tables or anything, but the search on this table can take upwards of 20+ seconds.

Any ideas?

[longneck]

1 million rows? fulltext? 20 seconds? sounds about right.

what happens if you remove IN BOOLEAN MODE?

[Shpigford]

Just tried it, it might have have shaved off 2 seconds from the 20 seconds...so nothing significant enough.

[longneck]

ok, that was a test to make sure you had a properly defined FULLTEXT index. you passed.

what do you get for this query?

SELECT count(*) 
FROM `comments` 
WHERE `type` = 'forum' 
  AND `active` = 'Y' 
  AND `item_id` != 45

[Shpigford]

Haha, glad I passed.

That query gets me 108,056.

[longneck]

i would check two things.

first, try forcing use of an index that covers (type, active, item_id).

second, make sure your server is configured with a large enough index cache to keep all your table indexes in memory.

[Shpigford]

I'm trying force the use of an index and am getting:
"Can't find FULLTEXT index matching the column list"

Here's the query:

SELECT `id`, `parent` FROM `comments` USE INDEX(item_id_active_type_id) 
WHERE MATCH(title, body) AGAINST('*search term*') AND `type` = 'forum' AND `active` = 'Y' AND `item_id` != 45

The item_id_active_type_id is an index on item_id, active, type, and id.

[longneck]

crap, i forgot that when you force an index, mysql won't also use the FULLTEXT index.

so back to my other suggestion: make sure your server is configured with a large enough index cache to keep all your table indexes in memory. also, use LOAD INDEX to preload your FULLTEXT index in to memory.

[Shpigford]

I really haven't ever used LOAD INDEX for anything. Is it something that needs to be run periodically or just once or what?

[Shpigford]

Also, how can I tell what size cache I need?

[cpellizzi]

I've done a little research on this over the last couple of days (but I'm not an expert). What I would do first is obviously make the fulltext index on title,body then do separate indexes on type, active and itemid or a single index on type, active then itemid. The single index on all 3 will work on any queries that have type, type and active, or type, active and itemid in the where clause so this might not be the best way to go if other queries aren't in that order. It sounds to me like the order of the current index on those 3 columns might render the index useless for that query. Have a look at http://www.sitepoint.com/article/opt...ql-application, I found it quite helpful.

[longneck]

Also, how can I tell what size cache I need?

add up the size of all of the MYI files in your data directory. add 10% for block alignment overhead. if your dataset is growing at a significant rate, make sure to factor that in, too.

[Shpigford]

@longneck: Is there a specific parameter that you would set to "make sure your server is configured with a large enough index cache"? I guess I'm just not following exactly what I would be configuring.

[longneck]

the parameter is key_buffer_size. so if you have 24 MB of MYI files and you decide that 32 MB is enough to account for a decent amount of future growth, add key_buffer_size=32M to your my.ini file.

[Shpigford]

We have ~625M of MYI files and key_buffer is set to 768M. So seems like the there is enough space allocated to the cache.