Efficient way to get last record from the database

Question

I have a database with following table structure :

id | entry | log_type  | user_id | created_on   |
------------------------------------------------|
1  |a      | error     | 1       | 1433752884000|
2  |b      | warn      | 2       | 1433752884001|
3  |c      | error     | 2       | 1433752884002|
4  |d      | warn      | 4       | 1433752884003|

I want to obtain the last record from the table based on created_on field, currently i am using the following query to obtain the result list and obtain the last record on it using java:

select * from log_table l where l.user_id=2 and l.log_type = 'error' order by l.created_on desc;

I am using JPA and i execute the query using .getResultList() on the Query interface .Once i get the result list i do a get(0) to obtain the desired last record .

I have a large table with too much data , above query takes too long to execute and stalls the application. I cannot add additional index for now on existing data . Apart from adding the index on the data is there an alternate approach to avoid stalling of this query .

I was thinking of executing the following query,

select * from log_table l where l.user_id=2 and l.log_type = 'error' order by l.created_on desc limit 1;

Currently i cannot execute my second query on the database as it might cause my application to stall. Will execution of the second query be faster than the first query ?

I don't have a sufficiently large dataset available to reproduce the stalling problems on my local system and hence . I tried executing the queries on my local database and due to the lack of the available large dataset , unable to determine if the second query would be faster with the addition of "limit" on the returned query.

If the above second query isn't supposed to provide a better result , what would be the approach that i should to get an optimized query .

In case the second query should be good enough to avoid stalling , is it due to the reason that the DB fetches only one record instead instead of the entire set of records ? does the database handle looking/fetching for a single record differently as compared to looking/fetching too many records (as in first query) to improve query timings.

Answer 1

The performance depends...

ORDER BY x LIMIT 1

is a common pattern. It may or may not be very efficient -- It depends on the query and the indexes.

In your case:

where l.user_id=2 and l.log_type = 'error' order by l.created_on desc

this would be optimal:

INDEX(user_id, log_type, created_on)

With that index, it will essentially do one probe to find the row you need. Without that index, it will scan much or all of the table, sort it descending (ORDER BY .. DESC) and deliver the first row (LIMIT 1)

Answer 2

Before you do your query.getResultList(), you need to query.setMaxResults(1). This is the equivalent of LIMIT 1.

But be aware that if your Entity has a Collection of related sub-objects JOINed to it in the query, the Entity Manager may still have to do an unbounded select to get all the data it needs to build the first Entity. See this question and answer for more information about that.

In your case, as you only need one Entity, I would recommend lazy-loading any attached Entities after you have done the initial query.