[MySQL] Fulltext search 'using filesort' - Hoe kom ik hieraf

[RvdH]

Beste,

Als wij op onze testserver een fulltext search doen zonder een 'order by', dan gaat dit razendsnel en werkt het goed.
Echter als wij een 'order by id' oid doen dan gaat MySQL over op filesorting -- en dit is supertraag!

Hoe kom ik hier vanaf?
Tabledef:

code:

mysql> describe books;
+---------------+---------------------+------+-----+------------+----------------+
| Field         | Type                | Null | Key | Default    | Extra          |
+---------------+---------------------+------+-----+------------+----------------+
| RecordNumber  | int(11)             |      | PRI | NULL       | auto_increment |
| MemberNumber  | int(11)             |      | MUL | 0          |                |
| BookNumber    | varchar(20)         |      | MUL |            |                |
| Author        | varchar(60)         |      | MUL |            |                |
| Titel         | text                |      |     |            |                |
| Description   | varchar(100)        |      |     |            |                |
| DescripRest   | text                |      |     |            |                |
| Price         | bigint(20) unsigned |      |     | 0          |                |
| Keywords      | varchar(100)        |      |     |            |                |
| DollarPrice   | bigint(20) unsigned |      | MUL | 0          |                |
| CountryNumber | tinyint(3) unsigned |      | MUL | 0          |                |
| EntryDate     | date                |      | MUL | 0000-00-00 |                |
| Status        | char(1)             |      | MUL |            |                |
| RecordType    | tinyint(4)          |      | MUL | 0          |                |
| BookFlags     | smallint(6)         |      | MUL | 0          |                |
| Year          | smallint(6)         |      | MUL | 0          |                |
| FirstEdition  | tinyint(4)          |      | MUL | 0          |                |
| DustWrapper   | tinyint(4)          |      | MUL | 0          |                |
| Signed        | tinyint(4)          |      | MUL | 0          |                |
| Cover         | tinyint(4)          |      | MUL | 0          |                |
| SpecialField  | tinyint(4)          |      | MUL | 0          |                |
+---------------+---------------------+------+-----+------------+----------------+

Indices:

code:

-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| books |          0 | PRIMARY         |            1 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | BookFlags       |            1 | BookFlags     | A         |          47 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | CountryBook     |            1 | CountryNumber | A         |          19 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | CountryBook     |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | MemberBook      |            1 | MemberNumber  | A         |         436 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | MemberBook      |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | DollarBook      |            1 | DollarPrice   | A         |       11914 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | DollarBook      |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | FirstBook       |            1 | FirstEdition  | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | FirstBook       |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | YearBook        |            1 | Year          | A         |        1001 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | YearBook        |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | EntryBook       |            1 | EntryDate     | A         |         897 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | EntryBook       |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | RecordTypeBook  |            1 | RecordType    | A         |           3 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | RecordTypeBook  |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | DustWrapperBook |            1 | DustWrapper   | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | DustWrapperBook |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | SignedBook      |            1 | Signed        | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | SignedBook      |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | CoverBook       |            1 | Cover         | A         |           3 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | CoverBook       |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | StatusBook      |            1 | Status        | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | StatusBook      |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | BookBook        |            1 | BookNumber    | A         |     1620356 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | BookBook        |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | SpecialBook     |            1 | SpecialField  | A         |           2 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | SpecialBook     |            2 | RecordNumber  | A         |     3240712 |     NULL | NULL   |      | BTREE      |         |
| books |          1 | totidx          |            1 | Author        | A         |     1620356 |     NULL | NULL   |      | FULLTEXT   |         |
| books |          1 | totidx          |            2 | Titel         | A         |     1620356 |        1 | NULL   |      | FULLTEXT   |         |
| books |          1 | totidx          |            3 | Description   | A         |     3240712 |     NULL | NULL   |      | FULLTEXT   |         |
| books |          1 | totidx          |            4 | DescripRest   | A         |     3240712 |        1 | NULL   |      | FULLTEXT   |         |
| books |          1 | totidx          |            5 | Keywords      | A         |     3240712 |     NULL | NULL   |      | FULLTEXT   |         |
+-------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
33 rows in set (0.00 sec)

Query zonder order by:

code:

mysql> select Titel,Price from books where match(Author,Titel,Description,DescripRest,Keywords) against ('+press' in boolean mode) limit 0,500;
[...]
500 rows in set (0.01 sec)
mysql> describe select Titel,Price from books where match(Author,Titel,Description,DescripRest,Keywords) against ('+press' in boolean mode) limit 0,500;
+-------+----------+---------------+--------+---------+------+------+-------------+
| table | type     | possible_keys | key    | key_len | ref  | rows | Extra       |
+-------+----------+---------------+--------+---------+------+------+-------------+
| books | fulltext | totidx        | totidx |       0 |      |    1 | Using where |
+-------+----------+---------------+--------+---------+------+------+-------------+
1 row in set (0.00 sec)

Query met order by:

code:

mysql> select Titel,Price from books where match(Author,Titel,Description,DescripRest,Keywords) against ('+press' in boolean mode) limit 0,500;
[...]
500 rows in set (13.03 sec)
mysql> describe select Titel,Price from books where match(Author,Titel,Description,DescripRest,Keywords) against ('+press' in boolean mode) order by RecordNumber desc limit 0,500;
+-------+----------+---------------+--------+---------+------+------+-----------------------------+
| table | type     | possible_keys | key    | key_len | ref  | rows | Extra                       |
+-------+----------+---------------+--------+---------+------+------+-----------------------------+
| books | fulltext | totidx        | totidx |       0 |      |    1 | Using where; Using filesort |
+-------+----------+---------------+--------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

We zouden het erg fijn vinden om eindelijk de fulltext search te kunnen gebruiken, op het moment hebben we een eigen dictionary en zoekmethode..

[slm]

afgezien van de problemen die MySQL sowieso heeft met het DESC sorteren en een where clause, wil je in je query sorteren op een index die niet in de fulltext index staat (of zelfs kán staan). Ik denk dat dit het probleem met name veroorzaakt. (EDIT: het is volgens mij zelfs zo dat de fulltext index zich helemaal niet leent om te sorteren, dus dat je met een dergelijke query+orderby altijd aan een filesort vast zit)

Als je een versie 3 van MySQL gebruikt zou je kunnen proberen of een upgrade naar versie 4 iets uithaalt in jouw geval. Met 'normale' order by desc queries is versie 4 een enorme vooruitgang. Geen idee hoe dat met fulltext queries zit in 4. Ik heb de fulltext optie van MySQL namelijk al een hele tijd geleden afgezworen.

[ Voor 15% gewijzigd door slm op 16-09-2003 19:11 ]

[RvdH]

Dit is versie 4.0.15.
We hebben het met versie 3 ook al geprobeerd, en toen ook al opgegeven.

Voorlopig blijven we dus maar gewoon onze eigen methode gebruiken, en eventueel gaan we de sourcecode van MySQL aanpassen om wel goed te sorteren. Aangezien we dit al eerder hebben gedaan zou het niet zo heel erg veel tijd moeten kosten.. maar het blijft lastig om met elke nieuwe MySQL versie opnieuw alles te moeten implementeren.

[slm]

andere database overwegen? Oracle bv?

[RvdH]

Oracle is veels te duur, en te traag voor onze andere, simpele selects.
Wellicht dat we het een en ander kunnen converteren naar SQLite, of misschien een ander, memory-based (server heeft 8G geheugen) zelf geschreven serverformat..

[slm]

Ach ja, ik weet niet wat de marge op boeken is tegenwoordig maar Oracle licenties zijn idd behoorlijk duur, maar het zelf (her)schrijven van bv MySQL trouwens ook, waarbij het zoals je zelf al aangaf iedere keer weer geheel of gedeeltelijk opnieuw moet gebeuren.