Efficient way to get last record from the database

jay

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.

Rick James

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)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to get last inserted record from core database?

From Dev

How to get last 5 mins record from SQL database table?

From Dev

How to get last 5 mins record from SQL database table?

From Java

Most efficient way to get the last element of a stream

From Dev

Whats the best way to get the nth to last record?

From Dev

Efficient way to get last n records from multiple tables sorted by date (user log of events)

From Dev

What is the most efficient way to record siblings in a relational database?

From Dev

How to return last record from database in codeigniter?

From Dev

Only getting last record from mysql database

From Dev

Rails efficient method to query table from last record backward or reverse

From Dev

Spring Data - get last record from the table

From Dev

Get the last 7 days record from Today

From Dev

Get Last Five Record From Table

From Dev

Get last record from each different id

From Dev

Get last record from Core data base

From Dev

What is the most efficient way to get the last line break in a string

From Dev

Efficient way to get last 10 rows of each group in SQLite

From Dev

An efficient way to get dictionary from dataframe

From Dev

An efficient way to get dictionary from dataframe

From Dev

WPF: Most efficient way to remove the last item from a bound list?

From Dev

WPF: Most efficient way to remove the last item from a bound list?

From Dev

How to get last record from column if there is no any unique ID for this record?

From Dev

Laravel - get id from the right database record

From Dev

Get only one record from android database

From Dev

Read last record from database without any unique id

From Dev

Efficient way to search single record from 6000k+ record DB in laravel

From Dev

PHP: Efficient way to use objects as products from database

From Dev

Most efficient way to select lots of values from database

From Dev

PHP: Efficient way to use objects as products from database

Related Related

  1. 1

    How to get last inserted record from core database?

  2. 2

    How to get last 5 mins record from SQL database table?

  3. 3

    How to get last 5 mins record from SQL database table?

  4. 4

    Most efficient way to get the last element of a stream

  5. 5

    Whats the best way to get the nth to last record?

  6. 6

    Efficient way to get last n records from multiple tables sorted by date (user log of events)

  7. 7

    What is the most efficient way to record siblings in a relational database?

  8. 8

    How to return last record from database in codeigniter?

  9. 9

    Only getting last record from mysql database

  10. 10

    Rails efficient method to query table from last record backward or reverse

  11. 11

    Spring Data - get last record from the table

  12. 12

    Get the last 7 days record from Today

  13. 13

    Get Last Five Record From Table

  14. 14

    Get last record from each different id

  15. 15

    Get last record from Core data base

  16. 16

    What is the most efficient way to get the last line break in a string

  17. 17

    Efficient way to get last 10 rows of each group in SQLite

  18. 18

    An efficient way to get dictionary from dataframe

  19. 19

    An efficient way to get dictionary from dataframe

  20. 20

    WPF: Most efficient way to remove the last item from a bound list?

  21. 21

    WPF: Most efficient way to remove the last item from a bound list?

  22. 22

    How to get last record from column if there is no any unique ID for this record?

  23. 23

    Laravel - get id from the right database record

  24. 24

    Get only one record from android database

  25. 25

    Read last record from database without any unique id

  26. 26

    Efficient way to search single record from 6000k+ record DB in laravel

  27. 27

    PHP: Efficient way to use objects as products from database

  28. 28

    Most efficient way to select lots of values from database

  29. 29

    PHP: Efficient way to use objects as products from database

HotTag

Archive