mysql union limit problem

Question

I want a paging script working properly basically but the situation is a bit complex. I need to pick data from union of two sql queries. See the query below. I have a table book and a table bookvisit. What I want is here to show all books for a particular category in their popularity order. I am getting data for all books with atleast one visit by joining table book and bookvisit. and then union it with all books with no visit. Everything works fine but when I try to do paging, I need to limit it like (0,10) (10,10) (20,10) (30,10), correct? If I have 9 books in bookvisit for that category and 3761 books without any visit for that category (total of 3770 books), it should list 377 pages , 10 books on each page. but it does not show any data for some pages because it tries to show books with limit 3760,10 and hence no records for second query in union. May be I am unable to clear the situation here but if you think a bit about the situation, you will get my point.

SELECT * FROM ( 
SELECT * FROM (
 SELECT viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
 INNER JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
 ORDER BY viewcount DESC 
 LIMIT 10, 10
 ) AS t1 
 UNION 
 SELECT * FROM 
 ( 
 SELECT   viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
 LEFT JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
 AND viewcount IS NULL 
 ORDER BY viewcount DESC 
 LIMIT 10, 10
  ) AS t2 
  ) 
  AS qry
   ORDER BY viewcount DESC 
LIMIT 10

score 4 · Answer 1 · edited Feb 27 '13 at 16:11

4

Do not use limit for the separate queries. Use limit only at the end. You want to get the hole result set from the 2 queries and then show only the 10 results that you need no matter if this is LIMIT 0, 10 or LIMIT 3760,10

SELECT * FROM (  
 SELECT * FROM (  
  SELECT viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
  INNER JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
  ORDER BY viewcount DESC   
 ) AS t1   
 UNION   
 SELECT * FROM  
 (   
  SELECT   viewcount, b.isbn, booktitle, stock_status, price, description FROM book AS b 
  LEFT JOIN bookvisit AS bv ON b.isbn = bv.isbn WHERE b.price <> 0 AND hcategoryid = '25' 
  AND viewcount IS NULL   
  ORDER BY viewcount DESC   
 ) AS t2   
)   
 AS qry  
ORDER BY viewcount DESC   
LIMIT 10, 10

edited Feb 27 '13 at 16:11

Arjen

1,321
8
10

answered Dec 08 '10 at 10:13

Yasen Zhelev

4,045
3
31
56

Youy are showing all book with visits folowed by all the books with no visit in certain category, right? So you do not need to have LIMIT in the subqueries of the UNION. You just need to LIMIT the resultl according to the pagination. Your query should be something like that (SELECT all_books_with_visits) UNION (SELECT all_books_with_no_visit) LIMIT 3760,10. The last LIMIT should be the only one in your query. Right now you have 2 additional LIMITs in each subquery that are causing the problem. – Yasen Zhelev Dec 08 '10 at 14:59
1

I have the same problem with pagination through MySQL. @Yasen Zhelev your solution works, but as g.b. already said, if the subqueries will return 10,000 results, wouldn't this decrease the performance to limit only after union? – MartijnvdB Jan 26 '12 at 09:55
I do not think that having a limit on a big result set will decrease performance. The UNION forms a combined result from its queries and then LIMITs it. You can run some test and see the time with and without LIMIT. – Yasen Zhelev Jan 30 '12 at 10:08
3

Seems it will select ALL from each subquery and AFTER that, apply the limit, so I think you are wrong on this one @YasenZhelev. Check this EXPLAIN: http://cl.ly/image/0w0y211M2s0U – Bart Apr 10 '14 at 18:38

score 0 · Answer 2 · answered Jun 03 '20 at 14:11

old one, but still relevant.

Basically, performance wise, you have to use LIMIT on each query involved into UNION, if you know there will be no duplicates between result sets you should consider using UNION ALL, again, performance wise. Then, if you need, lets say, LIMIT 100, 20, you do LIMIT each query with 120 (OFFSET + LIMIT), you are always fetching twice as much records you need, but not all.

SELECT [fields] FROM
(
   (SELECT [fields] FROM ... LIMIT 10)
   UNION ALL
   (SELECT [fields] FROM ... LIMIT 10)
) query
LIMIT 0, 10

5th page

SELECT [fields] FROM
(
   (SELECT [fields] FROM ... LIMIT 50)
   UNION ALL
   (SELECT [fields] FROM ... LIMIT 50)
) query
LIMIT 40, 10

score 0 · Answer 3 · answered Nov 08 '21 at 19:32

A decade after this question was asked, I can offer a solution, one that perhaps seems obvious to anyone familiar with views: instead of attempting a nested select statement to combine the two tables, use CREATE VIEW (or CREATE OR REPLACE VIEW) to combine the two tables into a view. The speed performance may be poor, as the tables will have to be combined for every page access and may have to be recombined for every pagination, depending on how your code is arranged, but it will work.

If you run into SQL user permissions issues that you and your sysadmin cannot solve, my best advice is to create a new user with full permissions, assign the new user to the table, and use the new user to create the views. That was the only thing that worked for me.

mysql union limit problem

3 Answers3

Linked