Before I get into this, I should point out that I hope the 216 seconds isn't with the ORDER BY RAND(). If it is, remove it and re-run. If that's the culprit, I'll show you a better way to handle that.
I've seen MySQL, especially on a few earlier versions, have some trouble with SELECTs on more than 6 tables. It's not that MySQL can't actually do the joins quickly; but the optimizer can tend to look a little too exhaustively for the best path...I've seen instances where the EXPLAIN took 20 seconds and the query actually took less than a second...
In this case, I don't quite think that's what's going on here, because for 8 tables, I can't imagine MySQL would spend 6 minutes trying to decide the optimal join path. What I'd suggest (before getting into STRAIGHT_JOIN or any hints), is cutting one table from the statement at a time, and trying to determine which is the sticking point in the statement.
From what I can surmise, MySQL has actually picked a mostly appropriate join strategy; there are no particular weak points in the access patterns, however, I did notice that the following:
tblAds.sellDate > DATE_ADD(tblAds.sellDate, INTERVAL -1 MONTH)
is an expression that will never be met. I don't believe, though, that MySQL has optimized away this expression. This could be a cause for some slowdown. I don't know if you actually meant the following:
tblAds.sellDate > DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)
or not, but that makes more sense.
Just for kicks, try running this variation:
SELECT
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
AND tblAds.enabled = 1
AND tblAds.deleted = 0
LEFT JOIN tblResorts
ON tblAds.resort_id = tblResorts.resort_id
AND tblResorts.country = 'US'
INNER 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.sellDate IS NULL OR tblAds.sellDate > DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH))
AND tblAdAdTypes.adType_id = 2
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
I formatted the code a bit for readability, and did a number of important things:
1) I took that WHERE expressions for the tblAds and tblResorts tables and moved them into the LEFT JOIN's ON condition for each of the respective tables. This means that MySQL will perform a filter for these conditions _before_ the join is performed, which will a) reduce the number of rows joined, and b) be what I think you originally intended.
2) I removed the DISTINCT because you already had a GROUP BY clause
3) I removed the tblBedrooms and tblBathrooms which were LEFT JOINed to tblAds. These tables were not used in either the SELECT or the WHERE conditions, and because it was an outer join to tblAds, served no purpose at all.
4) I changed the DATE_ADD expression to what I noted above.
Let me know if these changes help.
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com