I think I may need to optimize this query



  • From a MySQL slow query log:
    <font face="Arial" size="2"></font>

    <font face="Arial" size="2"># Time: 070329 15:54:39<o:p></o:p>
    # User@Host: bortman[bortman] @  [x.x.x.x]<o:p></o:p>
    # Query_time: 18446744073709551614  Lock_time: 0  Rows_sent: 1  Rows_examined: 0<o:p></o:p>
    </font>

    <font face="Arial" size="2">SELECT url FROM url_info WHERE url_id = '12345';<o:p></o:p></font>


    (data obviously anonymized slightly)

    Seems like 584 trillion years just might be an unacceptable response time.



  • This isn't the cause of the bug, but why is your ID a string?
     



  • @dmitriy said:

    This isn't the cause of the bug, but why is your ID a string?

    It never hurts to put a literal in quotes, even if that literal is numeric.  Some database engines will actually fail to use the index if you put a bare number in there, because the optimiser mistakenly interprets it as something that needs computation.

    It is a very good habit to put quotes around all literals in SQL.


     



  • @Critter said:

    It never hurts to put a literal in quotes, even if that literal is numeric.  Some database engines will actually fail to use the index if you put a bare number in there, because the optimiser mistakenly interprets it as something that needs computation.

    It is a very good habit to put quotes around all literals in SQL.

    Definitely, if for nothing more than preventing SQL injection attacks (along with other appropriate special character escaping measures). This is, of course, unless you're using a database library that allows for parameterized queries, like ADO.NET or something (I'm sure Java's probably got one too).



  • Better question, are there still any language around that doesn't allow parametrized query? (And yes, java has, for a long time)



  • maybe the clock was changed while the query was running?



  • @tchize said:

    Better question, are there still any language around that doesn't allow parametrized query? (And yes, java has, for a long time)

     

    Does PHP have them yet? The many shortcomings of its database libraries are one of the reasons I refuse to use or recommend PHP to anyone. I think the rest of the language is hopeless as well... if it's taking them this long to support namespaces, there must be something fundamentally wrong with how they designed/implemented the symbol table in the first place.



  • @Critter said:

    It is a very good habit to put quotes around all literals in SQL.


     



    Is it? Nobody's ever told me that :P
    I always cast numbers to an integer (and use mysql_real_escape_string on strings), so I'm not worried about SQL Injection.



  • @Critter said:

    It never hurts to put a literal in quotes, even if that literal is numeric.
    Unless you use some obsecure SQL driver (not to say names, but the very old lotus notes SQL driver is one of them) which actualy crashes and burns if you do a query on a numberic value with quotes. Which was only fixable by rebooting the machine. Ofcourse that driver was a huge WTF on it's own, combined with the project it was used in it was front page worthy. Luckly I no longer have to sourcecode or any relation to it.



  • @bortman said:

    From a MySQL slow query log:
    <font face="Arial" size="2"></font>

    <font face="Arial" size="2"># Time: 070329 15:54:39<o:p></o:p>
    # User@Host: bortman[bortman] @  [x.x.x.x]<o:p></o:p>
    # Query_time: 18446744073709551614  Lock_time: 0  Rows_sent: 1  Rows_examined: 0<o:p></o:p>
    </font>

    <font face="Arial" size="2">SELECT url FROM url_info WHERE url_id = '12345';<o:p></o:p></font>


    (data obviously anonymized slightly)

    Seems like 584 trillion years just might be an unacceptable response time.


    That's not 584 trillion years - that's FILE_NOT_FOUND!

     

    (ok, ENOENT really...) 


     


Log in to reply