Quick way to search first record

0

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 |
+----+-------------+-----------+-------+--------------------------+------+---------+------+------+-------------+
    
asked by anonymous 25.10.2018 / 18:55

0 answers