John Davidson

Message:

I have a query that fetches a set of records as shown:

select id 

from wallactions 

where status=1 and created > DATE_ADD(NOW(),INTERVAL -1 DAY) 

order by id desc LIMIT $priorRecordsCnt

The LIMIT variable $priorRecordsCnt changes often and can grow very large. It's necessary because i need to know the last id in that set of records based on the $priorRecordsCnt count value.

The problem is, I need to only access the last id in this set, as shown:

$last=array_values(array_slice($result,-1))[0]['id'];

I feel like this is pretty expensive to just get the id of the last record in the set.

Is there a way to optimize this query so it uses the count variable $priorRecordsCnt but i don't need to fetch all the records just to obtain the last value?

---

My Answer:
One way to optimize this query is to use a subquery to get the last record id without fetching all the records. Here's an example of how you can modify your query to achieve this:

php
SELECT id
FROM (
SELECT id
FROM wallactions
WHERE status=1 and created > DATE_ADD(NOW(),INTERVAL -1 DAY)
ORDER BY id DESC
LIMIT $priorRecordsCnt
) AS subquery
ORDER BY id ASC
LIMIT 1


This query uses a subquery to fetch only the necessary records based on the $priorRecordsCnt count value. Then, it orders the results in ascending order and limits the result to only the first record, which will be the last id in the set.

This way, you can avoid fetching all the records just to obtain the last id in the set.