Hello everybody,
I have a big problem with the Full Text index functionality with MySQL 3.23.
In fact, I really don't understand what is happening...
I have a table built this way :
CREATE TABLE MyTable (
Id bigint unsigned NOT NULL,
Num varchar(20) NOT NULL,
Title varchar(150) default NULL,
Aut1 varchar(100) default NULL,
Aut2 varchar(100) default NULL,
Aut3 varchar(100) default NULL,
Aut4 varchar(100) default NULL,
Aut5 varchar(100) default NULL,
....
Note text default NULL,
Abs text default NULL,
Tab text default NULL,
PRIMARY KEY (Id),
FULLTEXT (Title),
FULLTEXT (Aut1,Aut2,Aut3,Aut4,Aut5)
) TYPE=MyISAM;
This table contains approximatively 150 000 rows (and in the end, should
contain more than 1 000 000 rows).
I use queries like this :
SELECT count(*) FROM MyTable WHERE (MATCH(Title) AGAINST('a word'));
SELECT * FROM MyTable WHERE (MATCH(Title) AGAINST('a word')) LIMIT 10;
And the performance seems very weird to me :
1) If the query returns a few or no results at all, it is very fast
2) If the query returns a lot of results, it is very slow...
the more results returned, the more slower...
Sometimes (for > 10 000 results), it takes more than 40 seconds...
Why the "select count(*)" is so slow with a full text index when there is a
lot
of matching rows ? Why performance depends on the number of results ?
Why the "select * limit 10" is so slow with a full text index when there is
a lot
of matching rows ? Why performance depends on the number of results ?
I suppose that the problem is due to the fact that MySQL automatically
sorts (by relevance) all the results collected before producing the first 10
?
What can I improve performance significantly ?
Any idea ?
Please help me !
Thanks
Xavier LENOIR
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
