Browse By

SQL Speed Test: IN vs OR

Speed and efficiency are key factors when running a website. All code needs to be optimized, whether it is PHP, ASP, Python, or SQL. Internet users are notorious for having a low attention span, and a fast page load will help to keep their attention focused on what is on the page, rather that what is slowly being displayed on the page. This sparked some thought for me while I was at work the other day.

I work primarily out of an Oracle database and often have a list of values that a field needs to be checked against. In my opinion, it is nicer to look at the values in a list because it is cleaner and easier to follow. I wondered if the cleaner look of the “IN” statement was worth any performance difference between the “IN” and the “OR”. Both statements provide the same output when used correctly so I was curious if one was faster than the other. I then tested the difference, and the “IN” statement came up short.

This test was conducted using MySQL (Version 4.1.22) because all of my websites run on MySQL and, dare I say, most other websites do as well.

I used a test database I had readily available (WordPress) to test 2 SQL statements, 1 using “IN” and 1 using “OR.” Here are my results:

Results

The “IN” Statement

SELECT *
FROM wp_posts
WHERE post_author IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9 )

  • Records selected: 60
  • Execution Time: 0.0066 sec.
  • Average Time Per Row: 0.00011

The “OR” Statement

SELECT *
FROM
wp_posts
WHERE
post_author = 1 OR post_author = 2 OR post_author = 3 OR post_author = 4 OR post_author = 5 OR post_author = 6 OR post_author = 7 OR post_author = 8 OR post_author = 9

  • Records selected: 60
  • Execution Time: 0.0064 sec.
  • Average Time Per Row: 0.000107

Projections

Based on the average execution time per row, I have created a graph to show a scaling of the difference between execution times as the selected row count becomes larger. The results are as follows:

IN vs OR

Discussion

I have tested this on a small scale with a simple test database, but these results could be substantial on a larger scale. With major websites like Yahoo! storing 2 petabytes of data and Ebay processing 10 billion records per day, you can see how quickly .0002 seconds can become a problem. Major websites will definitely have a better and faster setup running than I do. They will also have incredibly optimized queries, but we (the smaller scale) should work hard and do our best to make our SQL easily scalable. A query that works on a table with 1,000 records may not work on a table with 1,000,000 records.

How do you optimize your SQL and database setup? Give your tips and tricks in the comments.



4 thoughts on “SQL Speed Test: IN vs OR”

  1. RMagnus says:

    I am curious to to see the statistics (ie: SQL Explain) that the DB Engine would report about the different queries.

    Having worked around database engines for several years*, I have seen a dramatic change in how the DB engines interpret, optimize, and execute the SQL we hand it.

    Only a few years ago, it seemed it was the burden of the programmer to implement efficient SQL code, but now the engines we use now are amazingly smarter, and are able to “re-interpret” the SQL we hand it. So I am wondering how Oracle engine above really interpreted your SQL.

    I am actually surprised from your findings, since in my experience “OR” branching always seems to be the longest path to follow.

  2. anonymous says:

    Did you run these tests immediately following each other? If you had the reduced time could be from the RDBMS having already loaded its cache.

  3. Dmitri P says:

    I agree that smaller samplings can be anecdotal.. Did the clock tick over a minute, did you move the mouse, etc. 10,000 – 100,000 rows would give you a repeatable result.

    It seems like this test weighs extra text on the wire (OR) against processing power.. DB extrapolation (IN).

    It might be fun to pursue this further down the path. Good article regardless.

    -dp

  4. Ankur says:

    This is a nice post. Really helpful πŸ™‚

    I also got similar info. in http://www.7tech.co.in/wordpress/how-to-add-a-wordpress-drop-down-menu-to-your-wordpress-theme/ which is also good. πŸ˜‰

Comments are closed.

%d bloggers like this: