table design and query opimization

[prasanna]

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.