0
I’m performing 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 too long, because the table in question is giant (90GB).
The columns id, device_id and time are indexed, but it still takes too long.
I executed:
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 |
+----+-------------+-----------+-------+--------------------------+------+---------+------+------+-------------+
Good afternoon friend, these 3 columns already have indexes id, device_id and team.
– Evanderson Pimentel
Then, there are other indexes that could help in this specific query if they exist. The problem is the impact they can have on the rest of your application.
– bfavaretto
I understand thanks friend. within the same situation, the Columa team is numeric timestamp, the best way to search for the first record is via ORDER BY really? or would have another way?
– Evanderson Pimentel
Depends. Since the query is using the time column index, try placing a new condition on that column, for example by filtering the records from the current day or time..
– bfavaretto