Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.