Index not being used

[Pauline C]

Hello,

I have a large table (51 million rows) that looks like below:

CREATE TABLE `iwm`.`newspectro_testing` (
`Measurement_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`Sample_Time` datetime DEFAULT NULL,
`Wavelength` double DEFAULT NULL,
`Absorption` double DEFAULT NULL,
`Detection_ID` bigint(20) DEFAULT NULL,
`Location_ID` int(11) DEFAULT NULL,
`Anomaly_Detected` int(11) DEFAULT '-1',
`Confidence` double DEFAULT '-99999',
`Channel0_Intensity` double DEFAULT NULL,
`Channel1_Intensity` double DEFAULT NULL,
PRIMARY KEY (`Measurement_ID`),
KEY `Sample_Time` (`Sample_Time`),
KEY `Index_sampleTime_locationID_anomalyDetected` (`Sample_Time`,`Location_ID`,`Anomaly_Detected`) USING BTREE,
KEY `Index_detectionID` (`Detection_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=69690644 DEFAULT CHARSET=latin1;


When I EXPLAIN the following query
SELECT absorption FROM newspectro_testing
WHERE sample_time > '2009-10-28 00:00:00' and location_ID = -1 AND anomaly_Detected = 0

It returns
'id','select_type','table','type','possible_keys','key','key_len','ref','rows','filtered','Extra'
-------------------------------------------------------------------------------------
'1','SIMPLE','newspectro_testing','all','Sample_Time, Index_sampleTime_locationID_anomalyDetected',NULL,NULL,NULL,'51028186','23.15','Using where'


I'm really confused to why MySQL is not using the compound index for the query. I've also created a test table with the same structure and copied 50,000 rows of data from newspectro_testing. However, when I EXPLAIN the same query on the testing table, the index Sample_Time is used. Furthermore, when I EXPLAIN a query like "SELECT absorption FROM newspectro_testing WHERE sample_time > '2009-10-28 00:00:00'", none of the index are used. But if the query is "SELECT absorption FROM newspectro_testing WHERE sample_time = '2009-10-28 00:00:00'", the Sample_Time index is used. I found it weird because I thought index can be used with =, >, <, >=, <=.

Any advice and help is greatly appreciated!

Pauline



Edited 1 time(s). Last edit at 11/16/2009 09:28AM by Pauline C.