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:
The “IN” Statement
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
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
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:
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.