BAD SQL - I've seen it happen, really!

[Chris]

So... in the shared hosting a DBA comes across many bad queries. For one reason or another a developer or admin or the guy who someone paid $10/hr writes SQL like this:

SELECT DISTINCT Resort.ResortID, Resort.Name, Resort.Address1, Resort.Address2, Resort.Address3, Resort.Address4
FROM Resort, ResortAttribute c2, ResortAttribute c3, ResortAttribute c4, ResortAttribute c5, ResortAttribute c6,
ResortAttribute c7, ResortAttribute c8, ResortAttribute c9, ResortAttribute c10, ResortAttribute c11, ResortAttribute c12,
ResortAttribute c13, ResortAttribute c14, ResortAttribute c15, ResortAttribute c16, ResortAttribute c17, ResortAttribute c18,
ResortAttribute c19, ResortAttribute c20, ResortAttribute c21
WHERE Resort.ResortID = c2.ResortID
AND Resort.ResortID = c3.ResortID
AND Resort.ResortID = c4.ResortID
AND Resort.ResortID = c5.ResortID
AND Resort.ResortID = c6.ResortID
AND Resort.ResortID = c7.ResortID
AND Resort.ResortID = c8.ResortID
AND Resort.ResortID = c9.ResortID
AND Resort.ResortID = c10.ResortID
AND Resort.ResortID = c11.ResortID
AND Resort.ResortID = c12.ResortID
AND Resort.ResortID = c13.ResortID
AND Resort.ResortID = c14.ResortID
AND Resort.ResortID = c15.ResortID
AND Resort.ResortID = c16.ResortID
AND Resort.ResortID = c17.ResortID
AND Resort.ResortID = c18.ResortID
AND Resort.ResortID = c19.ResortID
AND Resort.ResortID = c20.ResortID
AND Resort.ResortID = c21.ResortID
AND Resort.Name like '%%'
AND c2.AttributeID = 273
AND c3.AttributeID = 357
AND c9.AttributeID = 182
AND c10.AttributeID = 256
AND c11.AttributeID = 258
AND c12.AttributeID = 243
AND c13.AttributeID = 225
AND c14.AttributeID = 208
AND c15.AttributeID = 446
AND c16.AttributeID = 191
AND c17.AttributeID = 204
AND c18.AttributeID = 462
AND c19.AttributeID = 211
AND c20.AttributeID = 216
AND c21.AttributeID = 217
AND (c4.AttributeID = 173
OR c5.AttributeID = 181
OR c6.AttributeID = 184
OR c7.AttributeID = 215
OR c8.AttributeID = 234)
ORDER BY Resort.Name;

*************************** 1. row ***************************
Table: Resort
Create Table: CREATE TABLE `Resort` (
`ResortID` int(11) NOT NULL auto_increment,
`Name` varchar(255) NOT NULL default '',
`Address1` varchar(100) default NULL,
`Address2` varchar(100) default NULL,
`Address3` varchar(100) default NULL,
`Address4` varchar(100) default NULL,
`Phone1` varchar(30) default NULL,
`Phone2` varchar(30) default NULL,
`Phone3` varchar(30) default NULL,
`URL` varchar(255) default NULL,
`Description` text,
`NumberOfUnits` mediumint(11) default NULL,
`AvgPrice` float default NULL,
`RatingOverall` float default NULL,
`RatingGuestRoom` float default NULL,
`RatingService` float default NULL,
`RatingCleanliness` float default NULL,
`RatingDiningExperience` float default NULL,
`RatingRecommendation` float default NULL,
`RatingCommercial` float default NULL,
PRIMARY KEY (`ResortID`)
) TYPE=MyISAM
1 row in set (0.00 sec)

*************************** 1. row ***************************
Table: ResortAttribute
Create Table: CREATE TABLE `ResortAttribute` (
`ResortID` int(11) NOT NULL default '0',
`AttributeID` int(11) NOT NULL default '0',
PRIMARY KEY (`ResortID`,`AttributeID`)
) TYPE=MyISAM
1 row in set (0.00 sec)

Now, I love a good SQL puzzle but this is ridiculous! I sometimes give the SQL above as an interview question and you would not believe how many DBAs cannot solve it.

Here is some background:
1. This was a real query
2. The person who wrote it made it incredible inefficient
3. It is easy to solve if you just sit back and look at it
4. The query took forever to return a result

Ready for the solution? Not so fast! I need someone to comment on this before I reveal the "What where they thinking" and "How to solve it".

[Chris]

Glad I could I get this out there as an "interesting" problem to solve. I'll see what I can do as far as running this on a larger data set. This was a problem I helped with over 3 years ago so the data is long gone.

Great job, by the way, and thx for spending time on this!

[Dov Endress]

My pleasure, this was a fun one to diagnose.

Incidentally, I made a minor tweak, calling DISTINCT against ResortID in both the outer select and sub-query as there may be two resorts with the same name and meeting the attribute conditions:

SELECT
DISTINCT(r1.ResortID)
, COUNT(r1.ResortID) as count1
, r1.Name
, r1.Address1
, r1.Address2
, r1.Address3
, r1.Address4
FROM
Resort r1
, ResortAttribute ra1
WHERE
ra1.ResortID = r1.ResortID
AND
r1.name like "%%"
AND
ra1.AttributeID IN (1,2,3)
AND
r1.ResortID =
(SELECT
DISTINCT(r2.ResortID)
FROM
Resort r2
, ResortAttribute ra2
WHERE
ra2.AttributeID in (5,7)
AND
r2.ResortID = r1.ResortID
)
GROUP BY r1.ResortID
HAVING count1 = 3;


Cheers,

Dov