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#
精彩评论