Dan Nelson wrote:
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".
Just a note... your query times here can't be trusted. You need to do
SELECT SQL_NO_CACHE * FROM ...
Because if you don't the second time you run the query it will take 0.0ms to return it.
Also loading the cache into memory won't speed up the SELECT * portion becuase it needs to read rows from disk and disk is evil.
The second time you run the query the filesystem buffer cache will probably have the blocks in memory. Note sure about WinXP as this is a red flag. But Linux will certainly do the right thing.
If you're still seeing performance hits try to do an OPTIMIZE TABLE to get contiguous blocks on disk.
If the table is small enough (32M did you say?) just use a HEAP table and load it all in memory. This will CERTAINLY keep your index in memory and all the rows will be in memory too so you'll get constant fast times...
Read up on memory tables... there are some gotchas you need to be careful of.. (like the data won't be there when you restart).
Kevin
--
Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat.
Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod!
Kevin A. Burton, Location - San Francisco, CA
AIM/YIM - sfburtonator, Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
