Last Two Entries

[ballouta]

Hello

 

How can I get the last two entries from 'book' table which has the 'id' as PK and autoincrement?

 

Thank You

[bh]

Hi,

 

Count your rows and then use LIMIT.

LIMIT offset, row count

 

Your offset will be "Rownums"-2 so LIMIT "Rownums"-2

[Mchl]

SELECT * FROM tableName ORDER BY ID DESC LIMIT 2

[bh]

@Mchl: yeah, its another solution, but why do you want to order the entire table? if id is a pk thats up to speed, is ok, but i think the counting and then the limit is much lower cost, than your order by.

[Mchl]

Right. Just realised your solution will work, but there's something missing from it

LIMIT "Rownums"-2,2

 

Also it's worth noting that COUNT(*) return results instantaneously in MyISAM, but not so in InnoDB.

[bh]

I ran a micro test.

 

In a 20K rows table (id, name):

SELECT * FROM xyz LIMIT 19998, 2

~0.0065s

---

SELECT * FROM xyz ORDER BY id DESC LIMIT 0, 2

0.0002s

 

The 2nd is much faster Your right

 

(And at first, still no the count(*) )

 

[edit]

Its understandable, cuz the ids are in sequence (1, 2, 3... 7, ... almost nothing to order.

 

 

[ballouta]

Thank you guys