Re: Statistics bottleneck on large table
Posted by:
Rick James
Date: August 22, 2010 04:45PM
When a test in the WHERE clause is a range, that will be the last field used from the INDEX. So, if you have
WHERE lat=... AND lon=... AND vt BETWEEN...
This index will use only the vt part:
INDEX(vt, lat, lon)
whereas either of these will be more efficient, and use the entire index:
INDEX(lat, lon, vt)
INDEX(lon, lat, vt)
If you really have multiple ranges
WHERE lat BETWEEN... AND lon BETWEEN... AND vt BETWEEN...
only the first field in the index (whichever field it is) will be used.
decimal(5,0) takes (I think) 3 bytes. Would SMALLINT SIGNED (+/-32K) or SMALLINT UNSIGNED (0..65K), which take 2 bytes, suffice? Smaller -> more cacheable -> faster.
TIMESTAMP takes 4 bytes; DATETIME takes 8.
(Please use SHOW CREATE TABLE, not DESCRIBE.)
How big is the MYI file? Or do SHOW TABLE STATUS LIKE 'test';
How much RAM do you have?
What is the value of key_buffer_size? (SHOW VARIABLES LIKE 'key_buffer%';)
It could be that you have the default (too small) cache for MyISAM.