CODEWITHSUNDEEP
mysqlfull-text-searchquery-optimizationtiming
andrea
andrea

Reputation: 381

Full Text query run slow first time and then fast

Anyone has some info about this behaviour? I'm running on the same db this query

SELECT
                mk.IdMedia                    
            FROM
                mediakeyword mk                  
            WHERE
                MATCH(mk.SearchText) AGAINST('+"france"' IN BOOLEAN MODE) > 0

The plan for this 

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "mk"    "fulltext"  "SearchText"    "SearchText"    "0" \N  "1" "Using where"

SearchText has a FullText Index and the engine is MyISAM

The strange behavior is that first time i run the query the computed time is 2,324 s the second time is 0,015 , why?

There is a way to instruct mysql to have the same second behaviour?

Upvotes: 0

Views: 269

Answers (1)

Rick James
Rick James

Reputation: 142518

FULLTEXT is available in InnoDB; consider migrating.

There are two things that can lead to "first is slow; second is fast":

  • The first time you run a query, it may need to fetch index and/or data blocks from disk. The second time, those blocks are cached in RAM, therefore much faster.

  • The "Query cache", if enabled, records queries and their resultsets. So, if exactly the same SELECT is run a second time, it can simply look up the result that was previously computed.

Upvotes: 2

Related Questions