All Discussions Tagged 'nightmare' - Everything MySQL2024-03-29T09:41:49Zhttp://everythingmysql.ning.com/forum/topic/listForTag?tag=nightmare&feed=yes&xn_auth=noBAD SQL - I've seen it happen, really!tag:everythingmysql.ning.com,2009-09-09:3993569:Topic:672009-09-09T03:50:41.000ZChrishttp://everythingmysql.ning.com/profile/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:<br />
<br />
SELECT DISTINCT Resort.ResortID, Resort.Name, Resort.Address1, Resort.Address2, Resort.Address3, Resort.Address4<br />
FROM Resort, ResortAttribute c2, ResortAttribute c3, ResortAttribute c4, ResortAttribute c5, ResortAttribute c6,<br />
ResortAttribute c7, ResortAttribute c8, ResortAttribute c9, ResortAttribute c10, ResortAttribute c11,…
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:<br />
<br />
SELECT DISTINCT Resort.ResortID, Resort.Name, Resort.Address1, Resort.Address2, Resort.Address3, Resort.Address4<br />
FROM Resort, ResortAttribute c2, ResortAttribute c3, ResortAttribute c4, ResortAttribute c5, ResortAttribute c6,<br />
ResortAttribute c7, ResortAttribute c8, ResortAttribute c9, ResortAttribute c10, ResortAttribute c11, ResortAttribute c12,<br />
ResortAttribute c13, ResortAttribute c14, ResortAttribute c15, ResortAttribute c16, ResortAttribute c17, ResortAttribute c18,<br />
ResortAttribute c19, ResortAttribute c20, ResortAttribute c21<br />
WHERE Resort.ResortID = c2.ResortID<br />
AND Resort.ResortID = c3.ResortID<br />
AND Resort.ResortID = c4.ResortID<br />
AND Resort.ResortID = c5.ResortID<br />
AND Resort.ResortID = c6.ResortID<br />
AND Resort.ResortID = c7.ResortID<br />
AND Resort.ResortID = c8.ResortID<br />
AND Resort.ResortID = c9.ResortID<br />
AND Resort.ResortID = c10.ResortID<br />
AND Resort.ResortID = c11.ResortID<br />
AND Resort.ResortID = c12.ResortID<br />
AND Resort.ResortID = c13.ResortID<br />
AND Resort.ResortID = c14.ResortID<br />
AND Resort.ResortID = c15.ResortID<br />
AND Resort.ResortID = c16.ResortID<br />
AND Resort.ResortID = c17.ResortID<br />
AND Resort.ResortID = c18.ResortID<br />
AND Resort.ResortID = c19.ResortID<br />
AND Resort.ResortID = c20.ResortID<br />
AND Resort.ResortID = c21.ResortID<br />
AND Resort.Name like '%%'<br />
AND c2.AttributeID = 273<br />
AND c3.AttributeID = 357<br />
AND c9.AttributeID = 182<br />
AND c10.AttributeID = 256<br />
AND c11.AttributeID = 258<br />
AND c12.AttributeID = 243<br />
AND c13.AttributeID = 225<br />
AND c14.AttributeID = 208<br />
AND c15.AttributeID = 446<br />
AND c16.AttributeID = 191<br />
AND c17.AttributeID = 204<br />
AND c18.AttributeID = 462<br />
AND c19.AttributeID = 211<br />
AND c20.AttributeID = 216<br />
AND c21.AttributeID = 217<br />
AND (c4.AttributeID = 173<br />
OR c5.AttributeID = 181<br />
OR c6.AttributeID = 184<br />
OR c7.AttributeID = 215<br />
OR c8.AttributeID = 234)<br />
ORDER BY Resort.Name;<br />
<br />
*************************** 1. row ***************************<br />
Table: Resort<br />
Create Table: CREATE TABLE `Resort` (<br />
`ResortID` int(11) NOT NULL auto_increment,<br />
`Name` varchar(255) NOT NULL default '',<br />
`Address1` varchar(100) default NULL,<br />
`Address2` varchar(100) default NULL,<br />
`Address3` varchar(100) default NULL,<br />
`Address4` varchar(100) default NULL,<br />
`Phone1` varchar(30) default NULL,<br />
`Phone2` varchar(30) default NULL,<br />
`Phone3` varchar(30) default NULL,<br />
`URL` varchar(255) default NULL,<br />
`Description` text,<br />
`NumberOfUnits` mediumint(11) default NULL,<br />
`AvgPrice` float default NULL,<br />
`RatingOverall` float default NULL,<br />
`RatingGuestRoom` float default NULL,<br />
`RatingService` float default NULL,<br />
`RatingCleanliness` float default NULL,<br />
`RatingDiningExperience` float default NULL,<br />
`RatingRecommendation` float default NULL,<br />
`RatingCommercial` float default NULL,<br />
PRIMARY KEY (`ResortID`)<br />
) TYPE=MyISAM<br />
1 row in set (0.00 sec)<br />
<br />
*************************** 1. row ***************************<br />
Table: ResortAttribute<br />
Create Table: CREATE TABLE `ResortAttribute` (<br />
`ResortID` int(11) NOT NULL default '0',<br />
`AttributeID` int(11) NOT NULL default '0',<br />
PRIMARY KEY (`ResortID`,`AttributeID`)<br />
) TYPE=MyISAM<br />
1 row in set (0.00 sec)<br />
<br />
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.<br />
<br />
Here is some background:<br />
1. This was a real query<br />
2. The person who wrote it made it incredible inefficient<br />
3. It is easy to solve if you just sit back and look at it<br />
4. The query took forever to return a result<br />
<br />
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".