On Sunday 11 June 2006 12:25 am, Octavian Rasnita wrote:
> Hi,
>
> I have the following table:
>
> CREATE TABLE `z` (
> `hash` varchar(16) NOT NULL default '',
> `title` varchar(255) NOT NULL default '',
> `body` text NOT NULL,
> FULLTEXT KEY `title` (`title`,`body`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> I have tried the following query:
>
> select sql_calc_found_rows hash, title, substr(body, 1, 250) as preview,
> match(title, body) against('deputat') as rank from z where match(title,
> body) against('deputat' in boolean mode) order by rank desc limit 0,20;
Depending on the language at task, you might be able to get a bit of a speed
increase if you pull the preview from the database and pull it into the
language. I'm not quite sure how optimized MySQL's substr operation is vs.
other languages.
> The problem is that this query takes more than 12 seconds, and for some
> other one-word searches it takes almost 30 seconds, and this is very
> much for a table with only less than 200.000 records.
I'm not quite sure how much that can be optimized. You might want to just
check the MySQL manual entry on fulltext searches for more information (and
user comments):
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
--
Chris White
PHP Programmer/DB Fawn
Interfuel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
