Speeding up mysql fulltext query
I am trying to figure out a way I can speed up their query. Its taking around 18 to 20 seconds depending on keywords.
This is the query:
SELECT s.year, s.type, s.slug, c.id, c.card_number, c.player, c.attributes, c.production, MATCH(c.player, s.type,s.year) AGAINST ('+2012 +chrome +harper' IN BOOLEAN MODE) AS score
FROM checklists c
INNER JOIN sets s
ON (c.sets_id = s.id)
WHERE MATCH(c.player, s.type,s.year) AGAINST ('+2012 +chrome +harper' IN BOOLEAN MODE)
AND c.sets_id = s.id
HAVING score > 0
ORDER BY score DESC
When I run the EXPLAIN for the query, this is what is returned:
As you can see the checklists table has 4843702 records so its rather large. Any ideas?