Multiple 'OR' or just IN

[ChilliBoy]

Hi,

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

Question

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

$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

[MattR]

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.

[Hierophant]

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.

[DR_LaRRY_PEpPeR]

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

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

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.

[DR_LaRRY_PEpPeR]

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.

+---------+----------------------+------+-----+---------+----------------+
| 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

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.