I'm doing the following query in MySQL:
select id from positions
where device_id = {$device_id} and p1 IS NOT NULL
order by time desc limit 1
But the query is taking a long time, since the table in question is giant (90GB).
The columns id
, device_id
, and time
are indexed, but still take a long time.
I performed:
EXPLAIN select id from TABLE
where device_id = $x and P IS NOT NULL order by TIME desc limit 1
And the result was:
+----+-------------+-----------+-------+--------------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+--------------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | positions | index | device_id,p1,device_id_2 | time | 9 | NULL | 306 | Using where |
+----+-------------+-----------+-------+--------------------------+------+---------+------+------+-------------+