MySQL Forums
Forum List  »  Performance

Optimizing a query
Posted by: Daniel Chirita
Date: August 04, 2005 04:23AM

Hello all.

I hope this is the right place where to post this topic.
I have a query like this:

SELECT iAddID, sEmail, sAddText, iPrice, iCurency, dtDateAdded, sAddress, sWeb, sPicturePath_1, sPicturePath_2, bIsActive, sSubcategoryName, tbl_subcategories.sLink AS tbl_subcategories_sLink, sCategoryName, tbl_categories.sLink AS tbl_categories_sLink, sCityAlias, tbl_cities.sLink AS tbl_cities_sLink, sProvinceName, tbl_provinces.sLink AS tbl_provinces_sLink, sAdTypeAlias, tbl_ad_types.sLink AS tbl_ad_types_sLink, sCountryName, tbl_countries.sLink AS tbl_countries_sLink 
FROM 
( 
	( 
		( 
			( 
				( 
					(
						tbl_ads left join tbl_subcategories on tbl_ads.iSubcategory = tbl_subcategories.iSubcategoryID 
					) 
					left join tbl_categories on tbl_subcategories.iParentCategoryID = tbl_categories.iCategoryID 
				) 
				left join tbl_cities on tbl_ads.iCity = tbl_cities.iCityID 
			) 
			left join tbl_provinces ON tbl_cities.iCityProvince = tbl_provinces.iProvinceID 
		) 
		left join tbl_countries ON tbl_provinces.iCountry = tbl_countries.iCountryID
	) 
	left join tbl_ad_types ON tbl_ads.iAdType = tbl_ad_types.iAdTypeID 
) 
WHERE tbl_ads.bIsActive = 1 AND tbl_categories.sLink like '%%' AND tbl_subcategories.sLink like '%%' AND tbl_ad_types.sLink like '%%' AND tbl_ads.dtDateAdded like '%%' AND tbl_ads.dtDateAdded > '2001-01-01' AND tbl_cities.sLink like '%%' AND tbl_provinces.sLink like '%%' 
ORDER BY dtDateAdded desc 
LIMIT 0,5

It takes almost 1s. In the tbl_ads table we have 14.000 records. In the tbl_subcategories we have 50 records. The tbl_cities contains 40 entries. The other tables contain less then 10 records each. In my opinion, the query runs quite slow.

What can I do to achieve the maximum out of this query? I nees to optimize just the query and perhaps the tables structure while I cannot tweak the MySQL server.

Thanks in advance.

Best regards,
Dani

Options: ReplyQuote


Subject
Views
Written By
Posted
Optimizing a query
2261
August 04, 2005 04:23AM
1457
August 04, 2005 09:10AM


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.