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