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:

    @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.

    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?


  • @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
    WHEN SomeField = SomeValue THEN 0
    WHEN SomeField = SomeOtherValue THEN 1
    ELSE 2
    END
    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.


  • 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
    MyTable

     

    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.

     




  • @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:

    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.

    Yes, that's the format I was talking about. That reference page doesn't show the other, more flexible CASE format.

    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.


Log in to reply