1

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?

1000111
  • 13,169
  • 2
  • 28
  • 37
MikelG
  • 459
  • 4
  • 16
  • You cannot use an aliased column in the where clause unless you make it a subquery. – 1000111 Aug 02 '16 at 04:13
  • Yeah, that's what i'm realizing, however, when I make it a subquery, the speed becomes abysmal (.0004 seconds on the subquery, > 10 seconds when put inside another 'select') Updated the question. – MikelG Aug 02 '16 at 04:18

2 Answers2

2

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.

Norbert
  • 6,026
  • 3
  • 17
  • 40
  • Fast indeed! .0007 seconds. With this my friend.... you have given me the pieces to solve the "random" problem in MySQL. Thank you! – MikelG Aug 02 '16 at 04:47
1

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.

1000111
  • 13,169
  • 2
  • 28
  • 37
  • This is much better, but still a little slow. Query takes 1.4 seconds to execute. I'm no expert in MySQL, so are there any tricks we could use to speed this up? – MikelG Aug 02 '16 at 04:44
  • Please let me know what's the execution time after adding a limit 1 at the end. (answer updated) – 1000111 Aug 02 '16 at 04:49
  • .0007 seconds! This will work as well! I'll run the 2 working solutions a few times and average the times. – MikelG Aug 02 '16 at 04:55
  • Adding limit at the end would immediately halt to check if more rows match the condition in the where clause in this case. – 1000111 Aug 02 '16 at 04:56