I have one MySQL Server (5.0.37) on a Linux Server that have many databases. Some of them (12) have each an identical table to log sensors data.
Schema of one table
CREATE TABLE `measuresHistory` ( `keyMeasureHistory` int(10) unsigned NOT NULL COMMENT 'Key of the measure.', `keyMeasurePoint` int(10) unsigned NOT NULL COMMENT 'Measure point associated with the value.', `date` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of the measure.', `value` double NOT NULL COMMENT 'Value of the measure.', PRIMARY KEY (`keyMeasureHistory`), KEY `iDate` (`date`), KEY `iKeyMeasurePoint_Date` (`keyMeasurePoint`,`date`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
One of the tables has incredibly long query time, doing the same query on other tables is extremely fast, example :
SELECT * FROM acs_spectotechnologies_com_enerconcept_0007.measuresHistory WHERE date >= "2013-05-01 00:00:00" AND date <= "2013-05-01 05:59:59" AND keyMeasurePoint IN(1,2,3,4) ORDER BY date;
Result : 1440 rows, 0.001sec (23487659 total rows)
SELECT * FROM acs_spectotechnologies_com_enerconcept_0005.measuresHistory WHERE date >= "2013-05-01 00:00:00" AND date <= "2013-05-01 05:59:59" AND keyMeasurePoint IN(1,2,3,4) ORDER BY date;
Result : 1440 rows, 3.703sec (11749327 total rows)
EXPLAIN command in MySQL show me that indexes are used for the table
acs_spectotechnologies_com_enerconcept_0007.measuresHistory but not for the table
I've tried to remove indexes, recreated them, no changes. The two tables are on the same MySQL server. Also tried to force using the index, without difference.
Anyone have an idea?