MySQL Forums
Forum List  »  Optimizer & Parser

Paging on Union queries
Posted by: Tobias Marx
Date: March 24, 2008 11:23AM

I am testing UNIONs or tables with many OR/AND critria on columns.

This is really fast for limited resultsets:

Zeige Datens├Ątze 0 - 9 (10 insgesamt, die Abfrage dauerte 0.0040 sek)

SQL-Befehl: FLUSH query cache;
SELECT SQL_NO_CACHE a.uname, a.age, a.country, a.region, a.area, a.lastvisit, DATE_FORMAT( DATE_SUB( a.lastactive, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wann, DATE_FORMAT( DATE_SUB( a.lastedit, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wannEdit, a.regdate, a.picture, a.paid, a.mobile, p.cryptedname
FROM fb_users AS a
LEFT OUTER JOIN fb_pictures AS p ON a.uid = p.uid
AND p.mainpic =1
INNER JOIN fb_area_gb r ON a.area = r.idnum
AND a.country =70
AND r.localArea LIKE '%london%'
LIMIT 10 , 10
UNION
SELECT SQL_NO_CACHE a.uname, a.age, a.country, a.region, a.area, a.lastvisit, DATE_FORMAT( DATE_SUB( a.lastactive, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wann, DATE_FORMAT( DATE_SUB( a.lastedit, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wannEdit, a.regdate, a.picture, a.paid, a.mobile, p.cryptedname
FROM fb_users AS a
LEFT OUTER JOIN fb_pictures AS p ON a.uid = p.uid
AND p.mainpic =1
AND a.town LIKE '%london%'
INNER JOIN fb_area_gb r ON a.area = r.idnum
AND a.country =70
LIMIT 10 , 10
UNION
SELECT SQL_NO_CACHE a.uname, a.age, a.country, a.region, a.area, a.lastvisit, DATE_FORMAT( DATE_SUB( a.lastactive, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wann, DATE_FORMAT( DATE_SUB( a.lastedit, INTERVAL 1 HOUR ) , '%d/%m/%Y' ) AS wannEdit, a.regdate, a.picture, a.paid, a.mobile, p.cryptedname
FROM fb_users AS a
LEFT OUTER JOIN fb_pictures AS p ON a.uid = p.uid
AND p.mainpic =1
AND a.postcode LIKE '%london%'
INNER JOIN fb_area_gb r ON a.area = r.idnum
AND a.country =70
LIMIT 10 , 10

However, is it still save to page on those UNION selects ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Paging on Union queries
5773
March 24, 2008 11:23AM
2727
September 28, 2008 03:51PM


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.