My MyISAM table has 315,000 rows. I have a fulltext index on product_name. Cardinality is 0.
query_cache_size = 20M
query_cache_type = 1
query_cache_limit = 2M
My query is:
select `id`,`product_name`,`description`,`price`,`image`,`colour`
from table
where MATCH (`product_name`) AGAINST (:search)
AND `price` BETWEEN :mincost
AND :maxcost
ORDER BY `price` ASC LIMIT :start, :limit
Sometimes I will try a search and it will take around 20-40 seconds, usually the first time I try or when I havnt tried a search for 10+ minutes. Other times it will return the results in about 2 seconds or less. Im guessing this is to do with caching? Something similar to Query taking very long the first time it runs
Lets say my search variable was 'test', does MySQL cache the query with the variable 'test'? So that the next time I search for 'test' the results would return quickly.
Or does it cache the actual search query regardless of what the search variable is so that whatever is searched for will return quickly?
EXPLAIN:
1 SIMPLE table fulltext price,FULLTEXT_product_name FULLTEXT_product_name 0 NULL 1 Using where; Using filesort
