Mysql – Two identical MySQL tables, one not using indexes

indexindex-tuningmyisamMySQL

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$$

Problem

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)

Doing the EXPLAIN command in MySQL show me that indexes are used for the table acs_spectotechnologies_com_enerconcept_0007.measuresHistory but not for the table acs_spectotechnologies_com_enerconcept_0005.measuresHistory.

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?

Best Answer

Finally I've been able to figure out the problem, yet I still don't know why it happenned!

I've found this answer.

Doing ALTER TABLE acs_spectotechnologies_com_enerconcept_0005.measuresHistory ENABLE KEYS; fixed the usage of keys for this table, now every requests are lightning fast!

Related Question