Re: combine this 2 simple querys into 1 query for performance.

[Jørgen Løland]

Hi Oshri,

I don't think you'll be able to do that using a straightforward query. I suggest you use a stored procedure:

create table t1 (i int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9);

create procedure myselect(startval int, num_rows int)
begin
  declare lmt int;
  set lmt= (select count(i) from t1 where i>startval);
  set lmt=greatest(0, num_rows-lmt);

  (select i from t1 where i>startval order by i limit num_rows)
  UNION ALL
  (select * from t1 order by i limit lmt);
end

call myselect(1,2);
i
2
3
call myselect(7,2);
i
8
9
call myselect(1,5);
i
2
3
4
5
6
call myselect(7,5);
i
8
9
1
2
3
call myselect(1,9);
i
2
3
4
5
6
7
8
9
1
call myselect(7,9);
i
8
9
1
2
3
4
5
6
7

Hope this helps,
Jørgen Løland
Software Engineer, MySQL, Oracle
jorgenloland.blogspot.com