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:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE c ALL NULL NULL NULL NULL 4843702 Using temporary; Using filesort
1 SIMPLE s eq_ref PRIMARY PRIMARY 4 sportscardslist.c.sets_id 1 Using where
As you can see the checklists table has 4843702 records so its rather large. Any ideas?