Re: preloading index into cache doesn't?

[Linda]

Thanks for the quick reply. Here are the steps I did:
(the key status value seems all right, but the query is slow)

1). restart the server

2). check the status before doing anything
mysql> show status like "%key%";
Com_preload_keys 0
Com_show_key 0
key_blocks_not_flushed 0
key_blocks_unused 314546
key_blocks_used 0
key_read_requests 0
key_reads 0
key_write_requests 0
key_writes 0

3). to preload index into default cache:
mysql> cache index inventory_table in default
mysql> LOAD INDEX INTO CACHE inventory_table
(It just took about 0.1sec to finish, is that too quick for the 20MB index file to be loaded into cache?)

4). check the status, yes, the key block number has been increased.
mysql> show status like "%key%";
Com_preload_keys 0
Com_show_keys 0
key_blocks_not_flushed 0
key_blocks_unused 294641
key_blocks_used 19905
key_read_requests 19905
key_reads 19905
key_write_requests 0
key_writes 0


5). using explain before hit the query
mysql> explain select * from inventory_table order by name limit 10000, 1000;
-- select_type: SIMPLE
-- table: inventory_table
-- type: index
-- possible_keys: NULL
-- key: name_i
-- key_len: 130
-- ref: null
-- rows: 200,000
(Yes, it is using index. But since the possible_keys is null, will this be the problem?)


6). select the query, still so slow
mysql> select * from inventory_table order by name limit 10000, 1000;
1000 rows in set (1 min 9.10 sec)

7). check the status again
mysql> show status like "%key%";
Com_preload_keys 0
Com_show_keys 0
key_blocks_not_flushed 0
key_blocks_unused 294641
key_blocks_used 19905
key_read_requests 20463
key_reads 19905
key_write_requests 0
key_writes 0
(only key_read_requests was getting increased, that should be right. But why is query still so slow?)

every time after I did the preloading index, the first several query are always very slow
just like they are not being cached. Is there any way to improve this?

-- Linda