SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Does MySQL optimize "c IN(1)" to "c=1" ?

    Will the MySQL query optimizer detect instances of IN that only pass one argument and use the = form instead on its' own, or is this something I should determine myself before I send the query to MySQL with something like PHP ?

    I don't remember where I heard it, but I remember hearing from somewhere that I should detect this myself and build my query to suit.

  2. #2
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What about "IN(1,2,3,3,3)" to "IN(1,2,3)" ?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by joebert View Post
    ... I remember hearing from somewhere that I should detect this myself and build my query to suit.
    nah, don't worry, you'll be fine

    the optimizer is a lot smarter than most people realize

    yes, IN (1,1,2,2,3,3) resolves to IN (1,2,3), and you probably couldn't measure the difference in execution time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Floridiot joebert's Avatar
    Join Date
    Mar 2004
    Location
    Kenneth City, FL
    Posts
    823
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason I'm wondering about the last part is I have a prepared statement and I need to use a variable number of items for the IN clause of that prepared statement.

    I know the maximum number of items before the statement is prepared, so it seemed to make sense for me to just pad the unused later entries with existing values instead of using zero. I figured those existing values would just get optimized away, whereas a zero would need to be looked up to determine it's invalid before being optimized away.

    Switching from a sub-query within that IN clause for each execution of the prepared statement, to the pre-determined values with duplicates in some cases, has resulted in a page generation time that's 10 times faster.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •