Re: Analyze index usage
Thanks, Jay.
I have used this explain statement and it is using the exact same indexes I had hoped it would be using (good news!). The bad news is that the following query still takes about 216 seconds to run (not acceptable I do not think).
SELECT DISTINCT tblAds.ad_id, tblResorts.name, tblResorts.city, tblResorts.state, tblResorts.country,
tblCountries.countryName, tblAds.week, tblAds.unit, tblAds.askingPriceInt, tblAds.askingPriceText,
tblAds.rentalPriceInt, tblAds.rentalPriceText, tblAds.willExchangeFor, MIN(tblAP.fileName) AS fileName1,
MIN(tblRP.fileName) AS fileaName2, tblAds.comments, tblAds.resort_id, tblAds.week1, tblAds.weekFloat,
tblAds.weekFlex, tblAds.sellDate
FROM tblAdAdTypes
LEFT JOIN tblAds ON tblAdAdTypes.ad_id = tblAds.ad_id
LEFT JOIN tblBedrooms ON tblAds.bedroom_id = tblBedrooms.bedroom_id
LEFT JOIN tblBathrooms ON tblAds.bathroom_id = tblBathrooms.bathroom_id
LEFT JOIN tblResorts ON tblAds.resort_id = tblResorts.resort_id
LEFT JOIN tblCountries ON tblResorts.country = tblCountries.country
LEFT JOIN tblResortPics tblRP ON tblAds.resort_id = tblRP.resort_id
LEFT JOIN tblAdPics tblAP ON tblAds.ad_id = tblAP.ad_id
WHERE tblAds.enabled = 1
AND tblAds.deleted = 0
AND (tblAds.sellDate IS NULL OR tblAds.sellDate > DATE_ADD(tblAds.sellDate, INTERVAL -1 MONTH))
AND (tblAdAdTypes.adType_id = 2)
AND (tblResorts.country = 'US')
GROUP BY tblAds.ad_id, tblResorts.name, tblResorts.city, tblResorts.state, tblResorts.country,
tblCountries.countryName, tblAds.week, tblAds.unit, tblAds.askingPriceInt, tblAds.askingPriceText,
tblAds.rentalPriceInt, tblAds.rentalPriceText, tblAds.willExchangeFor, tblResorts.profile, tblAds.sellDate
/*ORDER BY Rand(568)*/
Here is the result of the explain:
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","tblResorts","ref","PRIMARY,country,resort_id_country,resort_id_state","country",3,"const",1110,"Using where; Using temporary; Using filesort"
1,"SIMPLE","tblAds","ref","PRIMARY,resort_id,enabled,deleted,deleted_enabled_selldate,resort_id_enabled_deleted_sellDate","resort_id_enabled_deleted_sellDate",6,"dbtsr1.tblResorts.resort_id,const,const",7,"Using where"
1,"SIMPLE","tblBedrooms","eq_ref","PRIMARY,bedroom_id,bedroom_id_2","PRIMARY",4,"dbtsr1.tblAds.bedroom_id",1,"Using index"
1,"SIMPLE","tblBathrooms","eq_ref","PRIMARY,bathroom_id,bathroom_id_2","PRIMARY",4,"dbtsr1.tblAds.bathroom_id",1,"Using index"
1,"SIMPLE","tblAdAdTypes","ref","ad_id,adType_id,ad_id_adType_id","ad_id_adType_id",8,"dbtsr1.tblAds.ad_id,const",1,"Using index"
1,"SIMPLE","tblCountries","eq_ref","PRIMARY","PRIMARY",2,"dbtsr1.tblResorts.country",1,""
1,"SIMPLE","tblRP","ref","resort_id","resort_id",4,"dbtsr1.tblAds.resort_id",2,""
1,"SIMPLE","tblAP","ref","ad_id","ad_id",4,"dbtsr1.tblAds.ad_id",12,""