mysql select from n last rows

Question

I have a table with index  autoincrement  and integer value  The table is millions of rows long   How can I search if a certain number appear in the last n rows of the table most efficiently

User · Answer

Last 5 rows retrieve in mysql

This query working perfectly

SELECT * FROM (SELECT * FROM recharge ORDER BY sno DESC LIMIT 5)sub ORDER BY sno ASC

or

select sno from(select sno from recharge order by sno desc limit 5) as t where t.sno order by t.sno asc

User · Answer

because it is autoincrement  here s my take   Select   from tbl  where certainconditionshere  and autoincfield  gt    select max autoincfield  from tbl  -  n

User · Answer

Might be a very late answer  but this is good and simple   select   from table name order by id desc limit 5   This query will return a set of last 5 values last 5 rows  you  ve inserted in your table

User · Answer

Starting from the answer given by  chaos  but with a few modifications    You should always use ORDER BY if you use LIMIT   There is no implicit order guaranteed for an RDBMS table   You may usually get rows in the order of the primary key  but you can t rely on this  nor is it portable  If you order by in the descending order  you don t need to know the number of rows in the table beforehand  You must give a correlation name  aka table alias  to a derived table    Here s my version of the query   SELECT  id  FROM       SELECT  id    val      FROM  big table      ORDER BY  id  DESC     LIMIT  n   AS t WHERE t  val     certain number

User · Answer

Take advantage of SORT and LIMIT as you would with pagination  If you want the ith block of rows  use OFFSET    SELECT val FROM big table where val   someval ORDER BY id DESC LIMIT n    In response to Nir  The sort operation is not necessarily penalized  this depends on what the query planner does  Since this use case is crucial for pagination performance  there are some optimizations  see link above   This is true in postgres as well  ORDER BY     LIMIT can be done without sorting   E 7 1  Last bullet  explain extended select id from items where val   48 order by id desc limit 10   ---- ------------- ------- ------- --------------- --------- --------- ------- ------ -------------    id   select type   table   type    possible keys   key       key len   ref     rows   Extra          ---- ------------- ------- ------- --------------- --------- --------- ------- ------ -------------     1   SIMPLE        items   const   PRIMARY         PRIMARY   4         const      1   Using index     ---- ------------- ------- ------- --------------- --------- --------- ------- ------ -------------

User · Answer

I know this may be a bit old  but try using PDO  lastInsertId  I think it does what you want it to  but you would have to rewrite your application to use PDO  Which is a lot safer against attacks

[mysql] mysql select from n last rows

Examples related to mysql

Examples related to select

Examples related to query-optimization