Search mysql table starting from last row

Question

So, I know that there is no such thing as "last row" on relational databases but I couldn't find a better word to explain what I want to do. I have a table that looks like the following, where id is an AUTO_INCREMENT field and time is the current Unix Time Stamp. Note that I can have the same time stamp for different rows.

    ╔════╦════════════╦════════════╦═════════════╦════════════╗
    ║ id ║  field_1   ║   field_2  ║   field_n   ║    time    ║
    ╠════╬════════════╬════════════╬═════════════╬════════════╣
    ║  1 ║ data_field ║ data_field ║ data_field  ║ 1369748934 ║
    ║  2 ║ data_field ║ data_field ║ data_field  ║ 1369748935 ║
    ║  3 ║ data_field ║ data_field ║ data_field  ║ 1369748936 ║
    ║  4 ║ data_field ║ data_field ║ data_field  ║ 1369748936 ║
    ║  5 ║ data_field ║ data_field ║ data_field  ║ 1369748938 ║
    ║  6 ║ data_field ║ data_field ║ data_field  ║ 1369748939 ║
    ╚════╩════════════╩════════════╩═════════════╩════════════╝

What I want to do is a select that starts searching from the highest time field and since I know it is sorted from lowest to highest I want it to stop searching when the first row does not mach my WHERE clause because all the other rows won't match it as well. What I think MySQL does is search through all the rows but since I know my data structure and I know the location of the data I want I don't need to search through the entire table. Also if I use ORDER BY, I believe that all the contents of my WHERE clause will be executed first, so what I will be doing is basically search the entire row set and then order it. One important thing is I don't know how many rows will be retrieved in order to use a LIMIT clause.

This is an example query:

SELECT fields FROM table WHERE time > timestamp

So I want to get all the results starting from a given date up to now.

Thank you very much for your help.

Have you tried making a dual primary key made up of the id and the timestamp? You *want* to do the WHERE before the ORDER BY since the WHERE is an O(n) operation (faster with indexes, see: http://stackoverflow.com/a/13362951/431415 ). — SubSevn, May 28 '13 at 13:06
I think you mean timestamp and id (in that order) otherwise, every row would still need to be searched. — mcNux, May 28 '13 at 13:20
@AnmolGupta, yes. This problem was solved. After some research I found out that the answer given by Olivier Coilland makes sense and I went for it. — Vinícius Barros, Aug 11 '15 at 01:46

score 2 · Answer 1 · answered May 28 '13 at 13:07

What you're asking for is an index. I suggest you do some research into how b-tree indexes work.

If you had an index on the time field, the engine would essentially do what you're asking for - as the index would be ordered, it would "know" that it only needs to find the earliest point you're asking for and return everything after it.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

score 2 · Answer 2 · answered May 28 '13 at 13:11

2

Just put an index on the time stamp field. Mysql will take care of it for you.

answered May 28 '13 at 13:11

Tony Hopkinson

20,172
3
31
39

score 1 · Accepted Answer · answered May 28 '13 at 13:06

What I want to do is a select that starts searching from the highest time field and since I know it is sorted from lowest to highest I want it to stop searching when ...

I really don't think you can tell MySQL to stop querying after a certain condition is met.

As a matter of fact, I really don't think you should care either. Querying is the database job and it does it pretty well. If it's a matter of performance, you should not see any difference until many centuries :)

Search mysql table starting from last row

3 Answers3

Linked