timetofly
timetofly

Reputation: 3077

Is there any way to better optimize this fulltext MySQL search? CPU load too high

I've got a table with 2.5 million records that I need to do a fulltext search on. For example, let's say I'm doing a music search. I currently have this as the best and most relevant way to get results:

  SELECT *, 
         MATCH (title,artist,album) AGAINST ('+KEYwORD' IN BOOLEAN MODE) AS score 
    FROM `music` AS `Music`   
   WHERE MATCH (title,artist,album) AGAINST ('+KEYWORD' IN BOOLEAN MODE)  
ORDER BY `score` DESC  LIMIT 10

This seems very inefficient, but I just don't seem to know of a better way to do it. I have a ridiculously high CPU load, and I believe that this is causing the trouble. On top of that, I'm also doing the following query to get the total number of results for pagination:

SELECT COUNT(*) AS `count` 
  FROM `music` AS `Music`
 WHERE MATCH (title,artist,album) AGAINST ('+KEYWORD' IN BOOLEAN MODE)

My title, artist, and album columns are on varchar(255) right now... not sure if that's the best option either. I've got a fulltext on title, artist, and album, and regular indexes on those three separately (not sure if that's needed).

What would you suggest to get around this high CPU load that this search is causing?

EDIT: Here is the EXPLAIN, as requested. Sorry, I'm not sure how to properly format it on here...

mysql> EXPLAIN SELECT *, MATCH (title,artist,album) AGAINST ('+beatles' IN BOOLEAN MODE) AS score FROM `tracks` AS `Track` WHERE MATCH (title,artist,album) AGAINST ('+beatles' IN BOOLEAN MODE)  ORDER BY `score` DESC  LIMIT      10;
+----+-------------+-------+----------+---------------+--------+---------+------+------+-----------------------------+
| id | select_type | table | type     | possible_keys | key    | key_len | ref  | rows | Extra                       |
+----+-------------+-------+----------+---------------+--------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | Track | fulltext | search        | search | 0       |      |    1 | Using where; Using filesort |
+----+-------------+-------+----------+---------------+--------+---------+------+------+-----------------------------+
1 row in set (0.01 sec)

Upvotes: 1

Views: 827

Answers (2)

Rob Raisch
Rob Raisch

Reputation: 17357

For some ideas regarding optimizing full-text search in MySQL, see my response to How to optimize MySQL Boolean Full-Text Search? (Or what to replace it with?) - C#

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

See this identical question:

Optimizing mysql fulltext search

Upvotes: 1

Related Questions