MySQL Forums
Forum List  »  Full-Text Search

Fulltext search and filesort
Posted by: John Clampberg
Date: February 09, 2008 10:09AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Fulltext search and filesort
4474
February 09, 2008 10:09AM
2854
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.