Taking the long way 'round in SQL
-
I've been doing some consulting work speeding up a slow website that's written in PHP & MySQL. After watching the slow query log for a while, I came across this gem which took about 7 seconds to run:
SELECT count(distinct zart.muzenbr) AS instrument_number_albums,
zart.instr AS recordTitle
FROM zart,
zclass
WHERE (zart.instr is NOT NULL)
AND (zart.muzenbr=zclass.muzenbr)
AND ((IF (zclass.released>"2008-09-14" AND zclass.released<="2008-09-23",0.25,
IF(zclass.released>"2008-09-21",-1,
IF (DATEDIFF("2008-09-21",zclass.released)<30,'1',
IF (DATEDIFF("2008-09-21",zclass.released)<90,'3',
IF (DATEDIFF("2008-09-21",zclass.released)<180,'6',
IF (DATEDIFF("2008-09-21",zclass.released)<360,'12',0))))))) IN (0.25, -1, 1, 3, 6, 12))
AND ((IF (zclass.released>"2008-09-14" AND zclass.released<="2008-09-23",0.25,
IF(zclass.released>"2008-09-21",-1,
IF (DATEDIFF("2008-09-21",zclass.released)<30,'1',
IF (DATEDIFF("2008-09-21",zclass.released)<90,'3',
IF (DATEDIFF("2008-09-21",zclass.released)<180,'6',
IF (DATEDIFF("2008-09-21",zclass.released)<360,'12',0)))))))=0.25)
GROUP BY recordTitle
ORDER BY instrument_number_albums DESC
LIMIT 40;The cleaned up version takes 0.02 seconds to run:
SELECT count(distinct zart.muzenbr) AS instrument_number_albums,
zart.instr AS recordTitle
FROM zart,
zclass
WHERE (zart.instr is NOT NULL)
AND (zart.muzenbr=zclass.muzenbr)
AND zclass.released>"2008-09-14"
AND zclass.released<="2008-09-23"
GROUP BY recordTitle
ORDER BY instrument_number_albums DESC
LIMIT 40;Took me over an hour to rewrite the PHP that built up the initial SQL query, which is an indication of how convoluted the code is.
-
@Darthon said:
Took me over an hour to rewrite the PHP that built up the initial SQL query, which is an indication of how convoluted the code is.
Or your typing speed ;)
-
t..o..u..c..h..é...
-
Pfff... real men just do a select and sort the results in PHP. It's like blackjack. Whoever gets closer to the script's timeout limit without going over wins.
-
Looks like it was converted from an Excel formula. I'd never even seen an "IF" clause used in a SELECT statement until now.
-
@Aaron said:
Looks like it was converted from an Excel formula. I'd never even seen an "IF" clause used in a SELECT statement until now.
It's a legitmate MySQL function (more here). It's basically the same as the IIF() function in JET, or CASE expressions in other SQL dialects.
I believe ODBC also has an IF() function.
-
@Jeff S said:
It's a legitmate MySQL function
I know it's legitimate - just never seen it used instead of CASE. Hence my saying - looks like some noob stumbled on the function, figured it looked sorta like Excel's IF, and just copied and pasted from a formula somewhere. Just a guess.
-
@Aaron said:
I find this funny, as the tab right next to TDWTF is... an SQL CASE syntax reference page. Still, isn't CASE used for evaluating discreet values, instead of conditionals? I mean, it can handle "case 1", "case 3", "case 5" but how can I do "case foo < 3" and "case foo > 6" for example?@Jeff S said:
It's a legitmate MySQL function
I know it's legitimate - just never seen it used instead of CASE. Hence my saying - looks like some noob stumbled on the function, figured it looked sorta like Excel's IF, and just copied and pasted from a formula somewhere. Just a guess.
-
@danixdefcon5 said:
I find this funny, as the tab right next to TDWTF is... an SQL CASE syntax reference page. Still, isn't CASE used for evaluating discreet values, instead of conditionals? I mean, it can handle "case 1", "case 3", "case 5" but how can I do "case foo < 3" and "case foo > 6" for example?
Eh?
SELECT CASE
Looks like a conditional to me. This is pretty basic stuff... if you wanted to, you could nest them, as in WHEN SomeField > SomeValue THEN CASE [whatever...], but if you're doing a lot of that, there's probably something wrong with your design.
WHEN SomeField = SomeValue THEN 0
WHEN SomeField = SomeOtherValue THEN 1
ELSE 2
END
-
Yeah I don't get what danixdefcon5 wants either. I routinely do things like the following, albeit in MSSQL 2005. I guess it could be RDBMS specific.
SELECT CASE WHEN MONTH(SomeDateTime) BETWEEN 1 AND 3 THEN 'Qtr 1' WHEN MONTH(SomeDateTime) BETWEEN 4 AND 6 THEN 'Qtr 2' WHEN MONTH(SomeDateTime) BETWEEN 7 AND 9 THEN 'Qtr 3' WHEN MONTH(SomeDateTime) BETWEEN 10 AND 12 THEN 'Qtr 4' END AS Quarter FROM MyTable
-
@lpope187 said:
Yeah I don't get what danixdefcon5 wants either. I routinely do things like the following, albeit in MSSQL 2005. I guess it could be RDBMS specific.
SELECT
CASE
WHEN MONTH(SomeDateTime) BETWEEN 1 AND 3 THEN 'Qtr 1'
WHEN MONTH(SomeDateTime) BETWEEN 4 AND 6 THEN 'Qtr 2'
WHEN MONTH(SomeDateTime) BETWEEN 7 AND 9 THEN 'Qtr 3'
WHEN MONTH(SomeDateTime) BETWEEN 10 AND 12 THEN 'Qtr 4'
END AS Quarter
FROM
MyTableCASE has two formats, at least in MS SQL, so perhaps that's what the confusion is about regarding if it is flexible enough. The other way of writing a CASE is:
CASE expr WHEN val1 THEN result1 WHEN val2 THEN result2 ... etc ... END
That format is less flexible, but it's shorter and simpler when you just need to see if a single expression is equal to different values. Perhaps this is the CASE style that danixdefcon5 is thinking of.
-
@danixdefcon5 said:
I find this funny, as the tab right next to TDWTF is... an SQL CASE syntax reference page. Still, isn't CASE used for evaluating discreet values, instead of conditionals? I mean, it can handle "case 1", "case 3", "case 5" but how can I do "case foo < 3" and "case foo > 6" for example?
Maybe you should open that reference page up and take a look at it. Sounds like you could use it.
-
@Jeff S said:
Yes, that's the format I was talking about. That reference page doesn't show the other, more flexible CASE format.CASE has two formats, at least in MS SQL, so perhaps that's what the confusion is about regarding if it is flexible enough. The other way of writing a CASE is:
CASE expr WHEN val1 THEN result1 WHEN val2 THEN result2 ... etc ... END
That format is less flexible, but it's shorter and simpler when you just need to see if a single expression is equal to different values. Perhaps this is the CASE style that danixdefcon5 is thinking of.
I didn't think about using BETWEEN in a CASE expression, and without knowing I could use expressions in the WHEN..THEN sections, I just couldn't find how to do it. However, nice thing to learn!
-
@darkmattar said:
Maybe you should open that reference page up and take a look at it. Sounds like you could use it.
@danixdefcon5 said:
That reference page doesn't show the other, more flexible CASE format.
Perhaps I should have read the reference page first as well. Irony is also a bitch.