Re: Some interesting result
Well, I'm in a strange situation as for the moment.
I've created helper tables, eg to perform searches ordered by end_time,
I've created a separate table:
DROP TABLE IF EXISTS end_time;
CREATE TABLE end_time (
lot_id int unsigned not null,
category_id int unsigned not null,
end_time int unsigned not null,
title char(50) not null,
description text not null,
INDEX end_time (end_time),
FULLTEXT ttitle (title),
FULLTEXT ttitle_description (title, description)
) TYPE = MyISAM COMMENT = "Search helper";
And filled each with sample 20 000 records (instead of 50 000),
but this seems to be enough to estimate the speed.
It have helped much, the search times dropped twice, but 5-10
seconds were still too much.
Then, I've set key_cache_block_size to 2048, and run
`repair table end_time` (hmm now I understand, that
key_cache_block_size should influence the key indexes on the disk,
so it looks like repair haven't been needed).
Miracle ! Without joining with `category_map` and `lot`,
all the searches dropped to 0.01-0.02 seconds (WOW!), and with INNER JOINing
SELECT end_time.lot_id, end_time.description
INNER JOIN category_map
ON category_map.child_id = end_time.category_id AND category_map.parent_id = 10
INNER JOIN lot
ON lot.id = end_time.lot_id
MATCH(end_time.title, end_time.description) AGAINST ('+keyword' IN BOOLEAN MODE)
the searches don't cross the line of 0.50, most of them are 0.05-0.06 seconds.
It is mysql-4.1.6-gamma.
The exact steps to reproduce, can someone check that out ?
1. fill in the data with default config.
2. put key_cache_block_size = 2048 in my.cnf
3. restart server
4. check key_cache_block_size via SHOW variables LIKE '%block%'; For me there is 1536 (?!)
5. "SET GLOBAL key_cache_block_size = 2048";
6. go "REPAIR TABLE end_time" (the table with fulltext search you are querying)
7. here we are, all the MATCH() with ORDER BY queries take 0.01, only some of them are 0.02 secs,
Tried to reproduce again - it does work. But this time, the times haven't dropped such low.
50 rows 0.65 sec
50 rows 0.56 sec
621 rows 1.72 sec
50 rows 0.11 sec
50 rows 0.08 sec
50 rows 0.09 sec
50 rows 0.10 sec
50 rows 0.07 sec
Can someone explain what happens ?!