Hi,
I saw a couple of posts about this and thought I'd join the chorus. Running
version 4.1.7 on a WinXP system.
I have a table "mindata" containing just over 1 million lines of price/volume
data for exchange-traded stocks. It's for a .NET web application. There are
about 3500 different stocks in the table. Each has it's own ID number, and
each has about 300 lines corresponding to different dates:
mysql> describe mindata;
+-----------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------+------+-----+---------+-------+
| ID | int(5) | YES | MUL | NULL | |
| TimeStamp | date | YES | MUL | NULL | |
| Close | float | YES | | NULL | |
| Chg | float | YES | | NULL | |
| Open | float | YES | | NULL | |
| Volume | bigint(20) | YES | | NULL | |
| Low | float | YES | | NULL | |
| High | float | YES | | NULL | |
+-----------+------------+------+-----+---------+-------+
mysql> show index from mindata;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| mindata | 1 | TimeStamp | 1 | TimeStamp | A |
320 | NULL | NULL | YES | BTREE | |
| mindata | 1 | iID | 1 | ID | A |
3538 | NULL | NULL | YES | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
When I first turn on the computer queries are disturbingly slow!
mysql> Select * from mindata where ID = 2345;
+------+------------+-------+-------+-------+--------+-------+-------+
| ID | TimeStamp | Close | Chg | Open | Volume | Low | High |
+------+------------+-------+-------+-------+--------+-------+-------+
| 2345 | 2003-09-15 | 17.55 | -0.1 | 17.64 | 19200 | 17.5 | 17.64 |
| 2345 | 2003-09-16 | 17.58 | 0.03 | 17.5 | 9300 | 17.49 | 17.65 |
| 2345 | 2003-09-17 | 17.5 | -0.08 | 17.53 | 6500 | 17.46 | 17.6 |
<snip>
321 rows in set (1.58 sec)
Does this make sense? I could swear I've had much larger tables in the past
that were much faster. Anyway...
Next I attempt to load the keys into the cache:
mysql> load index into cache mindata;
+---------------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------------+--------------+----------+----------+
| megascanner.mindata | preload_keys | status | OK |
+---------------------+--------------+----------+----------+
1 row in set (0.00 sec)
Hmmm... It looks like it worked, but the index file on the disk is 34MB,
obviously it would take longer that 0.00 sec to load it.
Here's another query to see if the speed has improved (picking a different ID
number this time to avoid caching the results):
mysql> Select * from mindata where ID = 3345;
+------+------------+-------+-------+-------+---------+-------+-------+
| ID | TimeStamp | Close | Chg | Open | Volume | Low | High |
+------+------------+-------+-------+-------+---------+-------+-------+
| 3345 | 2003-09-15 | 42.56 | 0.52 | 42.05 | 274800 | 42.02 | 42.63 |
| 3345 | 2003-09-16 | 42.89 | 0.33 | 42.2 | 253866 | 42.2 | 42.99 |
| 3345 | 2003-09-17 | 40.27 | -2.62 | 40.7 | 1582500 | 39.55 | 41.35 |
| 3345 | 2003-09-18 | 40.78 | 0.51 | 40.27 | 397300 | 40.01 | 40.83 |
<snip>
321 rows in set (1.63 sec)
The query speed hasn't improved. It looks like Load Index Into Cache didn't do
anything at all.
But here's something that does get the indexes loaded into memory, apparently
as a side-effect: I can disable, then re-enable the keys. After doing this
the queries are blindly fast. Unfortunately there's no telling how long things
will remain in the cache. Sometimes it stays there all day, but sometimes not.
I don't see any way to get control over it. I can't find any clear
explanation of what's going.
mysql> alter table mindata disable keys;alter table mindata enable keys; (two
queries on one line)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (5.66 sec)
Now the queries are blindly fast:
mysql> Select * from mindata where ID = 1001;
+------+------------+-------+-------+-------+---------+-------+-------+
| ID | TimeStamp | Close | Chg | Open | Volume | Low | High |
+------+------------+-------+-------+-------+---------+-------+-------+
| 1001 | 2003-09-15 | 28.72 | -0.2 | 28.95 | 290600 | 28.6 | 29.03 |
| 1001 | 2003-09-16 | 28.85 | 0.13 | 28.72 | 454200 | 28.72 | 28.92 |
| 1001 | 2003-09-17 | 28.25 | -0.6 | 28.96 | 511800 | 28.17 | 28.96 |
| 1001 | 2003-09-18 | 28.63 | 0.4 | 28.43 | 403900 | 28.35 | 28.73 |
| 1001 | 2003-09-19 | 28.61 | -0.06 | 28.67 | 343333 | 28.49 | 28.8 |
<snip>
321 rows in set (0.02 sec)
So I'm hoping somebody who knows the inner workings of MySql can explain why
Load Index Into Cache doesn't work, and why my inadvertent work-around does?
Should I report it as a bug???
Best rgds
Phil
