Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL Select/Update Question

Options
  • 02-11-2010 2:10pm
    #1
    JohnK
    Registered Users Posts: 4,780 ✭✭✭


    Is there a limit on the number of values you can supply to an IN clause? By that I mean say my query is
    SELECT [id], [col1], [col2] FROM myTable WHERE [id] IN (1, 2, 3, 4, 5)
    Is there a limit in the number of values I can pass into the IN clause? What about for updates?

    Also just to clarify I'm not talking about having a sub-query in the IN clause its just a list of values.

    edit: Just to add, this is being done through PHP if that makes any difference.

    Thanks.


Comments

  • Options
    #2
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    No, not really. Though MySQL will often fall over if the query is too large (i.e. the actual text size of the query is too big) or the resultset is too large.

    I've always gotten into the habit of sorting the list in the IN clause, though I don't know if that even helps.

    If you're going to be doing huge lists of discrete values, the suspicion is that you may be doing something wrong, though not always.

    For example, if you're querying table A to retrieve a subset of A's Primary Keys and then inputting this list into the IN clause, you could perform it all much more efficiently using a simple join.


  • Options
    #3
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    The use or IN is similar to using OR in your where clause and may lead to inefficient query plans.

    Sometimes (depending on the data) it may be more efficient to write multiple select statements once for each value in the In and Union the results.

    you would have to test and verify.


  • Options
    #4
    JohnK
    Registered Users Posts: 4,780 ✭✭✭JohnK


    Thanks for that. I'd agree its not the best way to be doing things but I was handed something this morning that for no discernible reason was artificially limiting the number of values to 5 so I was just worried there was some form of reasoning behind it. Since I've been asked now to just remove those limits it could result in a couple of hundred inputs at some stage down the line.


  • Options
    #5
    Jagera
    Closed Accounts Posts: 910 ✭✭✭Jagera


    If its possible in your case...:

    Rather than using a huge IN clause, plug all of the values into a table (temporary table for example) and join to that

    SELECT [id], [col1], [col2] FROM myTable WHERE [id] IN (1, 2, 3, 4, 5)

    becomes (sorry this is pseudo SQL)

    insert into [ColumnList] ( ID )
    [All of your values]

    --then

    SELECT [id], [col1], [col2] FROM myTable
    INNER JOIN [ColumnList] on myTable.id = ColumnList.id


Advertisement