MySQL Forums
Forum List  »  Optimizer & Parser

table design and query opimization
Posted by: prasanna
Date: April 19, 2006 02:11AM

Hello
Please help me to optimize following query. I know I need to add indexes and may be change table structures but I am not sure how and what to do exactly. It is too slow with a very small number of users. I will be very thankfull for your help.

select DISTINCT login.uID, login.mem_date, login.dateUpdated, login.zipCode, fName, gender, dob, countries.name as countryName, statSin, statRel, statMar, statDiv, statPar, interestInDiet, interestInFitness, interestInActivity, interestInSupport, diet, exercise, weightLossGoal, aboutMe, lastLogin, user_pictures.path, user_pictures.caption, user_pictures.dateAdded, max(session. uSessionTime) as uSessionTime, privacy_settings.hideOnline, who, uLastAct ,match(exercise) against('running') as KeyRanking from ((((countries inner join login on login.countryID = countries.countryID) inner join zip_codes on login.zipCode = zip_codes.zipCode) left join user_pictures on login.uID = user_pictures.uID and user_pictures.isDefault = 1) left join session on login.uID = session.uID) left join privacy_settings on privacy_settings.uID = session.uID where (3963.19* (acos(sin('40.7874'/57.29577951)*sin(latitude/57.29577951)+ cos('40.7874'/57.29577951)* cos(latitude/57.29577951)* cos(('74.319'/57.29577951)-(longitude/57.29577951))))) <= 10 and floor( (unix_timestamp( now( ) ) - unix_timestamp( dob ) ) / ( 60 * 60 * 24 * 365 ))between 18 and 99 and ( login.interestInDiet = 1 or login.interestInFitness = 1 or login.interestInActivity = 1 or login.interestInSupport = 1 )and ( ( login.statSin = 1 ) or ( login.statRel = 1 ) or ( login.statMar = 1 ) or ( login.statDiv = 1 ) )and login.weightLossGoal = 1 and (CONCAT(' ',replace(login.exercise, ',',' , '),' ') like '% running %') and login.countryID = 240 and login.ban = 0 group by login.uID ORDER BY KeyRanking desc,((unix_timestamp( now( ) ) - ( if(uLastAct is null,0,uLastAct) + 1 ))) asc,lastLogin desc

thanks



Edited 1 time(s). Last edit at 04/19/2006 02:16AM by prasanna.

Options: ReplyQuote


Subject
Views
Written By
Posted
table design and query opimization
2778
April 19, 2006 02:11AM
1574
April 19, 2006 10:25PM


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.