MySQL Forums

For a certain operation I need to find the first record outside a certain time interval for a certain sensorid. My first guess for a query was:

SELECT * FROM samples WHERE sensorid=XXX AND timestamp >= 'XXXX-XX-XX XX:XX:XX' ORDER BY timestamp ASC LIMIT 0,1

SELECT * FROM samples WHERE sensorid=XXX AND timestamp <= 'XXXX-XX-XX XX:XX:XX' ORDER BY timestamp DESC LIMIT 0,1


However, for certain values of sensorid, these queries take a *very* long time to execute and shows up in the slow query log. It says in this log: 'Rows_examined: [almost the size of my table]'. It looks like MySQL doesn't use the indexes for sensorid and timestamp. Any idea what goes wrong here???



The table looks like this:

sampleid (primary key)
sensorid (bigint, indexed)
timestamp (datetime, indexed)
value (float)

It has over 20 million rows. The # of rows for a certain sensorid can be over 50k. The storage engine is InnoDB and the indexes are BTREEs. The database is running on a dedicated machine with 4GB RAM and serves no other databases.