Fulltext search and filesort
Hello there,
I m using Mysql 4.1.11 and I got a little problem concerning a fulltext search.
Assuming a 6M lines table with a structure like
MYTABLE
id - double
title - varchar(128)
desc - varchar(128)
usedcount - double
With index like
PRIMARY id
FULLTEXT ft_index (title(128), desc(128))
INDEX usedcount
When i query the table with :
SELECT id, title
FROM mytable
WHERE MATCH (title, desc) AGAINST ('keyword')
LIMIT 100
I got a pretty quick response and an explain plan like this :
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | mytable | fulltext | ft_index | ft_index | 0 | | 1 | Using where |
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
But problems come when i try to add a order by clause (by usedcount)
SELECT id, title
FROM mytable
WHERE MATCH (title, desc) AGAINST ('keyword')
ORDER BY usedcount DESC <--------------------Thats the new part
LIMIT 100
I got a very slow response and the explain plan display the damn 'Using filesort'
It seems that mysql doesn t take care of my index and only use the fulltext one.
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | mytable | fulltext | ft_index | ft_index | 0 | | 1 | Using where; Using filesort |
+----+-------------+-----------+----------+---------------+---------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
How can I avoid this filesort ?
Edited 6 time(s). Last edit at 02/10/2008 04:53PM by John Clampberg.
Subject
Views
Written By
Posted
Fulltext search and filesort
4482
February 09, 2008 10:09AM
2856
February 13, 2008 05:49PM
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.