I've got a table with just over 1 million rows and am trying to do the following fulltext search on it:
Here is the EXPLAIN output:
SELECT `id`, `parent`
WHERE MATCH(title, body) AGAINST('*search term*' IN BOOLEAN MODE)
AND `type` = 'forum'
AND `active` = 'Y'
AND `item_id` != 45
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.
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