Mysql Fulltext and Slow First Query
I have been struggling with MYSQL fulltext for some time and although I have made some great improvements some nagging issues still exist.
query_cache at 300mb
localserver(athlon 64x2) 4gb memory
My tables are:
The articles table contains 1.5 million rows of title,description data. I gave a up searching on this
and created a custom script that reads the data from articles and builds a comma seperated keywords
field in the search table with a coresponding unique id back to articles for the join.
Right now searching fulltext in phrase mode against the articles(article_name,keywords) fulltext index returns the results that I want but the FIRST query is really really slow 20+ seconds after the first query everything is lightning fast.
When I go to profile the query 'Sending Data' is where all my time goes, from what I've read this is because phrase searching throws out the limit clause and reads all db rows correct?
Are there any tactics to reduce the time on the first query?
After reading through numerous posts I don't think that my database and index sizes are large enough to be causing this much trouble? But I have already looked into Sphinx and Lucene as next steps.
Any advice would be awesome! Thank so much! Dave
Here is an example query and the explain.
articles.articleUrl, articles.Keywords, articles.Name, articles.articleId, sources.SourceName, sources.SourceImage, articles.SourceId
FROM articles inner Join sources ON articles.SourceId = sources.SourceId Inner Join search ON articles.articleId = search.article_id
WHERE sources.Active = '1' AND Match(search.text) AGAINST('programming, php')
Simple | search | fulltext | PRIMARY,idx_articleid,idx_text | idx_text | 0 | | 1 | using where
Simple | articles | eq_ref | primary, idx_articleid, idx_sourceid | primary | 4 | r2.search.article_id |1|
Simple | sources |eq_ref | primary,idx_sourceid | primary | 4 | r2.articles.SourceId | 1| using where