MySQL Forums
Forum List  »  Newbie

Index not being used
Posted by: Pauline C
Date: November 16, 2009 09:20AM


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`)

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
'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!


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

Options: ReplyQuote

Written By
Index not being used
November 16, 2009 09:20AM
November 18, 2009 05:21AM
November 18, 2009 10:48AM
November 18, 2009 05:06PM
November 19, 2009 03:09PM
November 19, 2009 05:13PM
November 19, 2009 07:30PM
November 19, 2009 09:35PM
November 20, 2009 08:36AM
November 20, 2009 07:28PM
November 23, 2009 09:21AM
November 23, 2009 10:23AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.