Re: increase the search speed

2006-06-12 Thread Chris White 
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]

increase the search speed

2006-06-11 Thread Octavian Rasnita 
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;

The table has almost 200.000 records.
I am using MySQL 5.0.16-standard.

I want to search the records that contain a certain word (or more
complicated expressions), so I need using a boolean mode search.
However, I want to return only the first 20 records ordered by rank, so
that's why I
also need to use a common search (not in boolean mode) for getting that
rank.

I have set MySQL to also index the 3 chars words.

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.

Can I do something to increase the search speed?

I think that maybe if I will change some MySQL settings, the search might
work faster, but I don't know what I need to change.

Thank you.

Teddy


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]