increasing the search speed 2006-06-02 - By Octavian Rasnita
Back
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 is made just for testing, with as few fields as possible (only 3).
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 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, and not only the words
with 4 or more chars because I need to be able to search for those words.
The problem is that this query takes more than 12 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.
I also have a list with stop words and the program I use doesn't search for
those words anyway. Can I tell MySQL to not index the words from that list,
and if yes, please tell me how. I have read some things in the docs and I
have tried them under Windows, but without any effect.
Can I configure MySQL to use that list of stop words without re-compiling
it?
I would also like to define some chars as word-chars, for example:
?????-?????
Can this be done without re-compiling MySQL? (But this is not so important.
The most important is the search speed increase.)
Thank you very much.
Teddy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
