MikelG MikelG - 4 months ago 7
SQL Question

How can I speed up this query with an aliased column?

So I found this code snippet here on SO. It essentially fakes a "row_number()" function for MySQL. It executes quite fast, which I like and need, but I am unable to tack on a where clause at the end.

select
@i:=@i+1 as iterator, t.*
from
big_table as t, (select @i:=0) as foo


Adding in
where iterator = 875
yields an error.

The snippet above executes in about .0004 seconds. I know I can wrap it within another query as a subquery, but then it becomes painfully slow.

select * from (
select
@i:=@i+1 as iterator, t.*
from
big_table as t, (select @i:=0) as foo) t
where iterator = 875


The snippet above takes over 10 seconds to execute.

Anyway to speed this up?

Answer

In this case you could use the LIMIT as a WHERE:

select 
   @i:=@i+1 as iterator, t.* 
from 
   big_table as t, (select @i:=874) as foo
LIMIT 875,1

Since you only want record 875, this would be fast.

Comments