开发者

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)


See this identical question:

Optimizing mysql fulltext search


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#

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜