SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Oct 2000
    Posts
    430
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple 'OR' or just IN

    Hi,

    Browsing SP for inspiration I came across the following thread (Key Points Cut Below): - http://www.sitepointforums.com/showt...threadid=39635

    Question
    PHP Code:
    Do you have any views on whether the following mysql query is efficient is there a better way of doing it

    $query "SELECT email FROM email_tbl WHERE email = '$email1' OR email = '$email2' OR email = '$email3' OR email = '$email4' "
    Answer
    PHP Code:
    $list = array($email1$email2$email3$email4);
    $query 'SELECT email FROM email_tbl WHERE email IN("' implode('","'$list) . '")'
    This is very applicable to many of my Queries as I have numerous "checkboxes" which if checked build ip a long OR query (Which could possibly get upto 150 OR's !!)

    I've never seen mention of the IN syntax and can't find it in Kevin's or Wrox's books so I was wondering if anyone can provide me with more information on it and if it is actually more efficient than using OR or just makes more readable code.

    Many Thanks in advance

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using IN( .. ) over OR , OR , OR etc. should provide no performance difference since their query plans should be identical. It depends on MySQL's optimizer but I would think it would take the IN and convert it to multiple ORs, so maybe a fraction of a second longer to execute.

    The advantage is that it saves you typing in your query.

  3. #3
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The main advantage of using the "IN" subclause is when you do not know what you are going to be comparing. This is less of a problem in MySQL since it doesn't allow sub-selects than it would be in other database engines.

    As Matt said though, the execution plan for each type of query should be the same and it won't add up to a savings in MySQL.
    Wayne Luke
    ------------


  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'd use IN(). it looks cleaner and since you don't have OR over and over, it should be faster for MySQL to parse the query.

    this is probably slightly different, but i've noticed that doing this

    SELECT * FROM table WHERE id=const

    is MUCH faster (2-3 times) than this

    SELECT column1, column2, column3, column4, column5, column6, column7, column8, column9 FROM table WHERE id=const

    in that case it may be b/c you're selecting columns and it's figuring out which order to put them in. but, i have still noticed on seemingly simple queries (in terms of search time/rows returned) that are "long," seem a little slow. it takes longer than i would've thought just to parse the query.

    i just benchmarked the 2. see for yourself:

    SELECT * FROM cities WHERE city='Chesterfield' OR city='St Louis' OR city='Columbus' OR city='Los Angeles' OR city='San Franciso' OR city='non existent city' OR city='Belleville' OR city='Hershey'

    average time: 0.0017s

    SELECT * FROM cities WHERE city IN('Chesterfield', 'St Louis', 'Columbus', 'Los Angeles', 'San Franciso', 'non existent city', 'Belleville', 'Hershey')

    average time: 0.0014s

    small, but there's definitely a difference with just 8 values. i rest my case about IN().

    EDIT: manual page for IN(): www.mysql.com/doc/C/o/Comparison_Operators.html
    Last edited by DR_LaRRY_PEpPeR; Jan 21, 2002 at 16:58.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That's interesting -- however without knowing what data is in your table, indexes and such I wouldn't bet the bank on a single example.

    For example, if the query plans are identical (all else held constant) but the initial optimization is faster then you'll see only a .0003s difference as the size of the table increases.

    However, if there is something neat with IN then the query times would increase as the size of the rows do..

    So if not too much trouble then maybe create 500,000 rows and fill it up with random data. Don't place an index on it and try the query with 1/2 of the rows matching and 1/2 not (e.g. the IN list contains 10 items, 5 of which exist in the table). Run the query -- really, the difference of thousanths-of-a-second is so so so small that it could be due to random fluctuations in CPU power, or data fragmentation on the disk, etc.

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by MattR
    That's interesting -- however without knowing what data is in your table, indexes and such I wouldn't bet the bank on a single example.
    Code:
    +---------+----------------------+------+-----+---------+----------------+
    | Field   | Type                 | Null | Key | Default | Extra          |
    +---------+----------------------+------+-----+---------+----------------+
    | cityid  | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
    | city    | varchar(36)          |      | MUL |         |                |
    | lat     | float(6,3)           |      | MUL | 0.000   |                |
    | lon     | float(6,3)           |      | MUL | 0.000   |                |
    | stateid | tinyint(3) unsigned  |      |     | 0       |                |
    +---------+----------------------+------+-----+---------+----------------+
    ~100 rows (just a table i had handy )

    For example, if the query plans are identical (all else held constant) but the initial optimization is faster then you'll see only a .0003s difference as the size of the table increases.

    However, if there is something neat with IN then the query times would increase as the size of the rows do..
    yeah, i get what you're saying. i would think you're probably right about it just being the initial parsing of the query -- that's why i was saying about ORs making the query longer. i imagine both are optimized the same once it starts searching the table, though. the output from explain is the same w/ ORs or IN(), except if you do this

    Code:
    mysql> EXPLAIN SELECT * FROM cities WHERE cityid=1 OR cityid=1;
    +--------+-------+---------------+---------+---------+-------+------+-------+
    | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
    +--------+-------+---------------+---------+---------+-------+------+-------+
    | cities | const | PRIMARY       | PRIMARY |       2 | const |    1 |       |
    +--------+-------+---------------+---------+---------+-------+------+-------+
    1 row in set (0.00 sec)
    
    mysql> EXPLAIN SELECT * FROM cities WHERE cityid IN(1, 1);
    +--------+-------+---------------+---------+---------+------+------+------------+
    | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra      |
    +--------+-------+---------------+---------+---------+------+------+------------+
    | cities | range | PRIMARY       | PRIMARY |       2 | NULL |    1 | where used |
    +--------+-------+---------------+---------+---------+------+------+------------+
    1 row in set (0.00 sec)
    in that case, IN() doesn't notice that the values are the same.
    Last edited by DR_LaRRY_PEpPeR; Jan 21, 2002 at 18:31.


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
  •