The 7 seconds query
The following query takes ages:
SELECT SQL_NO_CACHE DISTINCT 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 JOIN fb_pictures AS p ON a.uid = p.uid
WHERE a.country =70 and p.mainpic=1
GROUP BY a.uname
The aim is the following:
- one user can have 0..n pictures
- a search should retrieve all users - if they don't have a picture the joined column should be null - otherwise the main picture should be shown
- the number of search results should never be higher then the number of users
I am no thinking of doing 37 queries instead (outer loop + 36x inner loop)
and do a query on the pictures table and then on the users table....but I am sure there must be a more neat solution?
Any help is highly appreciated!
Thanks!
Tobias
p.s.: Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref country country 4 const 177740 Using where; Using temporary; Using filesort
1 SIMPLE p ref uid uid 4 usr_web2_1.a.uid 2
Edited 1 time(s). Last edit at 03/24/2008 05:53AM by Tobias Marx.
Subject
Views
Written By
Posted
The 7 seconds query
3393
March 24, 2008 05:50AM
2331
March 24, 2008 06:20AM
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.