Optimizing a query : joining a table on itself

Question

i want to get last two row of a table in one query as new data and previous data

i got

 select tbl.x , tbl2.x as last_x 
 from tbl left join tbl tbl2 ON tbl.id!= tbl2.id
 order by tbl.id desc , tbl2.id desc limit 1

it works fine but i think it might get slow in a big DB

is there any way to make this faster ?

@max Am I crazy or why does `SELECT * FROM tbl ORDER BY id DESC LIMIT 2` not do the trick? — fancyPants, Apr 09 '13 at 09:51
@tombom - I take it that he wants some details from both rows returned in a single record. — Kickstart, Apr 09 '13 at 09:54

Kickstart · Accepted Answer · 2013-04-09T09:59:33.963

2

A LIMIT should work in a basic subquery, and so the following will possibly be more efficient

SELECT Sub1.x , Sub2.x as last_x 
FROM (SELECT x FROM tbl ORDER BY tbl.id DESC LIMIT 1) Sub1
CROSS JOIN (SELECT x FROM tbl ORDER BY tbl.id DESC LIMIT 2, 1) Sub2

edited Apr 09 '13 at 09:59

answered Apr 09 '13 at 09:50

Kickstart

21,403
2
21
33

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

You can take a look at the execution plan and try to optimize your query, but usually you do this when you face a problem so you can determine which parts are taking long.

Chick this thread to: How to optimise MySQL queries based on EXPLAIN plan

But as saied i would not try to solve a problm which still does not exist, i do not actually see a problem aith your query.

edited May 23 '17 at 11:49

Community

1
1

answered Apr 09 '13 at 09:39

CloudyMarble

36,908
70
97
130

Optimizing a query : joining a table on itself

2 Answers2