Re: Some interesting result

[Aleksandr Guidrevitch]

Hi All,

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
like

SELECT end_time.lot_id, end_time.description
FROM end_time
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
WHERE
MATCH(end_time.title, end_time.description) AGAINST ('+keyword' IN BOOLEAN MODE)
ORDER BY
end_time.end_time;

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,
not more

Tried to reproduce again - it does work. But this time, the times haven't dropped such low.
Before reproduction:
50 rows 0.65 sec
50 rows 0.56 sec
621 rows 1.72 sec

After reproduction
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
etc

Can someone explain what happens ?!

Sincerely,
Aleksandr Guidrevitch