Query execution plan
I have a MySQL 5.1.57 database. The database has thousands of tables having the following structure.
CREATE TABLE `table_19` (
`time` bigint(20) NOT NULL,
`code` tinyint(3) unsigned NOT NULL DEFAULT '0',
`val1` float NOT NULL DEFAULT '0',
PRIMARY KEY (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs
/*!50100 PARTITION BY RANGE (time DIV 41561539529932800)
(PARTITION p130 VALUES LESS THAN (130) ENGINE = MyISAM,
PARTITION p131 VALUES LESS THAN (131) ENGINE = MyISAM,
PARTITION p132 VALUES LESS THAN (132) ENGINE = MyISAM,
PARTITION p133 VALUES LESS THAN (133) ENGINE = MyISAM,
PARTITION p134 VALUES LESS THAN (134) ENGINE = MyISAM,
PARTITION p135 VALUES LESS THAN (135) ENGINE = MyISAM,
PARTITION p136 VALUES LESS THAN (136) ENGINE = MyISAM,
PARTITION p137 VALUES LESS THAN (137) ENGINE = MyISAM,
PARTITION p138 VALUES LESS THAN (138) ENGINE = MyISAM,
PARTITION p139 VALUES LESS THAN (139) ENGINE = MyISAM,
PARTITION CatchAll VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
These tables continually archive timestamped experimental data and the reason for not having one big table is speed of recalling data.
Fetching a time span of values is very fast and consistent between different tables. However, I have noted that fetches of a single value closest to a desired time is eratic in the time to fetch. The query used is:
select * from table_288 where time <= 5706114878826086400 order by time desc limit 1
This query runs very fast on the majority of tables, but very slow on a few. The difference appears to be the execution plan. The fast queries use 'range' and the slow ones use 'index'. The following results were obtained by doing a fetch on every table. The data is fetch time in milliseconds, channel name (corresponds to a table), rows in the table, and execution plan. The optimizer's choice of plan does not appear to be on size of the table, as both 'index' and 'range' are used for both large and small tables. What is the optimizer's decision based on, since the table definitions are identical? Can I force it to do a 'range' plan?
msec channel events plan
6,382 ILM1L13B 79,038,587 index
6,083 ILM5R10B 79,016,924 index
5,365 ILM2R08B 103,471,328 index
4,242 ILM1H03B 111,476,494 index
3,513 ILM2L17B 46,970,487 index
2,893 ILM7D00B 50,218,967 index
1,830 ILM1H00AB 83,067,580 index
1,082 ILM1L15B 79,164,213 index
572 ILM1A19B 13,873,740 index
272 iocsl5:loadPercent 56,857,362 range
61 iochlc3:memfree 107,711 range
33 ILM1L18B 369,230 index
3 iocsl5:memblock 36,513 range
1 analog_P6v_ch1b_22 1,065,677 range
1 CAPNCS13 11 index
1 CEV0L05JT.MAX 444,692 range
1 CEV0L05RT.MIN 93 range
1 CEV1L06RT.KD 113 range
1 CEV1L09RT.KP 113 range
1 CEV1L18JT.MVAL 177 range
1 CEV2439.MVAL 806 range
1 CEV243BY.KP 240 range
1 CEV2454.KI 241 range
1 CEV2620.KP 248 range
1 CEV2L06JT.ORBV 2,343,423 range
1 CEV2L13RT.KP 107 range
1 CEV2L14JT.OVAL 48,923,198 range
1 CEV2L14SH.MMOD 228 range
1 CEV2L19SH.ORBV 4,451,912 range
1 CEV50SH.LOPR 183 range
1 CEV562CDA.OVAL 2,878 range
1 CEV562SHA.SMOD 107 range
1 CEV6712AS 95 range
1 CEV6731B.DMIN 197 range
1 CEV6851.DMIN 197 range
1 CEV71Q105 166 range
1 CEV71Q119.MIN 170 range
1 CEV72D116.OVAL 801,889 range
1 CEV72Q116.OVAL 1,531,374 range
1 CEV72Q216.OVAL 583,010 range
1 CEVFL01JT.KP 105 range
1 CEXP2ATR 1,047,856 range
1 CFI11138 18,181,524 range
1 CFI12523 20,685 range
1 CLL2L0850 1,274,016 range
...