MySQL Forums :: Newbie :: Index not being used


Advanced Search

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

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.

Options: ReplyQuote


Subject Written By Posted
Index not being used Pauline C 11/16/2009 09:20AM
Re: Index not being used Vadim Kiryukhin 11/18/2009 05:21AM
Re: Index not being used Pauline C 11/18/2009 10:48AM
Re: Index not being used Rick James 11/18/2009 05:06PM
Re: Index not being used Pauline C 11/19/2009 03:09PM
Re: Index not being used Guelphdad Lake 11/19/2009 05:13PM
Re: Index not being used Pauline C 11/19/2009 07:30PM
Re: Index not being used Rick James 11/19/2009 09:35PM
Re: Index not being used Pauline C 11/20/2009 08:36AM
Re: Index not being used Rick James 11/20/2009 07:28PM
Re: Index not being used Pauline C 11/23/2009 09:21AM
Re: Index not being used Rick James 11/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.