CODEWITHSUNDEEP
mysqlsqlexecution-timemysql-variables
MikelG
MikelG

Reputation: 479

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?

Upvotes: 1

Views: 47

Answers (2)

1000111
1000111

Reputation: 13519

Could you please try this?

Increasing the value of the variable in the where clause and checking it against 875 would do the trick.

SELECT
    t.*
FROM
    big_table AS t,
    (SELECT @i := 0) AS foo
WHERE
    (@i :=@i + 1) = 875
LIMIT 1

Caution:

Unless you specify an order by clause it's not guaranteed that you will get the same row every time having the desired row number. MySQL doesn't ensure this since data in table is an unordered set.

So, if you specify an order on some field then you don't need user defined variable to get that particular record.

SELECT
    big_table.*
FROM big_table
ORDER BY big_table.<some_field>
LIMIT 875,1;

You can significantly improve performance if the some_field is indexed.

Upvotes: 1

Norbert
Norbert

Reputation: 6084

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.

Upvotes: 2

Related Questions