Paging on Union queries
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 ?