MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Xavier LENOIR Date:October 14 2002 5:06pm
Subject:HELP : Performance with FULLTEXT Index
View as plain text  
Hi everybody,

I'm trying to use the Full Text index functionality with MySQL 3.23
and I have a problem I can't understand.

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'));

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 slower... the more
results returned
        the more slower...
        Sometimes (for > 10 000 results), it takes more than 35 seconds...

I have supposed that the problem is due to the fact that MySQL automatically
sorts
(by relevance ?) the results collected...
So, I try to add  a  "LIMIT 10" to the SELECT... but it does not change
anything at all...
Any idea ?

I really need your help...
Thanks
Xavier LENOIR





Thread
HELP : Performance with FULLTEXT IndexXavier LENOIR14 Oct