In the last episode (Dec 11), Philip Lane said: > 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: > > 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)
I'd say 95% of that time is spent reading the records, not reading the index. Try timing "select count(*) from mindata where ID = 2345". > 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) I can't explain this. Could be a bug. Happens on my 4.1.7/FreeBSD server too. > 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. ALTER TABLE commands rebuild the entire table, and because your table is so small, both the index and your data are still in your filesystem's cache after the rebuild is finished. > 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??? Another workaround would be to run a couple queries that pull the entire table into memory. Something like "select * from mindata having 1=0", which will return no rows but force it to read each record, should run pretty quick. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]