Hi there,
Is there any common strategy (of using indexes) to avoid filesorts while sorting searches by a field ?
Eg. I want fulltext search, then sort results by some other table field.
For example .:
create table article (
id int not null primary key auto_increment, sort1 int not null,
sort2 int not null,description text not null,
FULLTEXT description (description) )
explain select id from article where match (description) against ('cccc'in boolean mode) order by sort1;
mysql> mysql> explain select id from article where match (description) against ('cccc' in boolean mode) order by sort1;
+----+-------------+---------+----------+---------------+-------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+----------+---------------+-------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | fulltext | description | description | 0 | | 1 | Using where; Using filesort |
+----+-------------+---------+----------+---------------+-------------+---------+------+------+-----------------------------+
It doesn't matter whether I have index on sort1, filesort is always used. Furthermore, I've noted that adding an index slows the query down with each index added (suppose becuase of table index becomes bigger).
I come to a solution where I create a separate table (exact copy) with only
fulltext indexes, then query it with order by LEFT JOIN with original table,
eg:
SELECT article.id, article.description FROM search
LEFT join article
ON search.id = article.id
WHERE
MATCH (description) AGAINST ('some' IN BOOLEAN MODE)
ORDER BY search.sort1
LIMIT 0, 50;Also, I intensively use table article for listing (w/o search) items sorted by sort1 or sort2 fields, so in article table indexes for sort1 and sort2 are absolutely necessary.
So currently I see only 2 solutions 1. add to the article table ndescription field (with stemmed description), and fulltext index on it
2. create a separate table for this purpose.
I hope there is a 3rd variant, a good idea on how to avoid filesort
Sincerely, Aleksandr
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
