Fulltext and order by date... any way to optimize?
The following fulltext query runs in 10 seconds (for 1000 queries, a single user)
SELECT c.pmid, c.date_created, c.article_title, c.author_list, j.title, j.pub_date_year, j.pub_date_month, j.pub_date_day, j.volume, j.issue,
MATCH (a.abstract_text, a.article_title)
AGAINST ('rapid' IN BOOLEAN MODE) AS score
FROM abstract AS a, medline_citation as c, journal as j WHERE a.pmid = c.pmid AND a.pmid = j.pmid AND MATCH (a.abstract_text, a.article_title)
AGAINST ('rapid' IN BOOLEAN MODE) ORDER BY c.date_created DESC LIMIT 0 , 25
The same query without the order by is 10 times faster. The c.date_created field is indexed.
EXPLAIN yield Using where; Using temporary; Using filesort;
Not exactly optimal. Any way to do better?