Re: Index not being used

[Rick James]

The buffer pool setting is the most important for InnoDB.

Are these frequent values?
location_ID = -1
anomaly_Detected = 0
If so, that might explain your need for FORCE.

How many rows come back from the query? Thousands? Maybe 1m30s is reasonable for number of index items scanned and the number of rows returned.

What is the diff between the indexes?
(`Sample_Time`,`Location_ID`,`Anomaly_Detected`) -- the query has to step over entries with Location_ID other than -1 or anomoly_Detected other than 0.
(`Location_ID`,`Anomaly_Detected`, `Sample_Time`) -- the query needs to look at only exactly the entries that match your query. (Actually, it will read one extra entry to discover that it is finished.

Measurement_ID, the PK, is actually in the leaf nodes of each secondary index. So the query executes thus:
1. Drill down in the index to the first row after
location_ID = -1 AND anomaly_Detected = 0 and sample_time = '2009-10-28 00:00:00'.
2. Walk through the index
2.1. At each entry, use Measurement_ID to drilldown the PK BTree to find the row.
2.2. Output one row (absorption)
2.3. Stop when the WHERE clause no longer applies.

For this query, the following would make it run faster by never having to touch the data:
INDEX (`Location_ID`,`Anomaly_Detected`, `Sample_Time`, absorption)
The EXPLAIN would show "Using index".