MySQL Forums
Forum List  »  Optimizer & Parser

Re: combine this 2 simple querys into 1 query for performance.
Posted by: Jørgen Løland
Date: September 15, 2011 01:50AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: combine this 2 simple querys into 1 query for performance.
768
September 15, 2011 01:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.