MySQL Forums
Forum List  »  Full-Text Search

Mysql Fulltext and Slow First Query
Posted by: Dave Mendoza
Date: November 07, 2008 11:17AM

Hello,
I have been struggling with MYSQL fulltext for some time and although I have made some great improvements some nagging issues still exist.

Mysql 5.0.1
Myism tables
query_cache at 300mb
localserver(athlon 64x2) 4gb memory

My tables are:
articles(181mb)(idx:200mb),
sources(9k),
search(61mb)(idx:121mb)

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.

QUERY:

SELECT SQL_CALC_FOUND_ROWS
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')

EXPLAIN:

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql Fulltext and Slow First Query
7268
November 07, 2008 11:17AM
3644
December 04, 2008 09:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.