MYSQL OR vs IN performance

by

I needed to know this for sure, so I benchmarked both methods. I consistenly found IN to be much faster than using OR.

Do not believe people who give their “opinion”, science is all about testing and evidence.

I ran a loop of 1000x the equivalent queries (for consistency, I used sql_no_cache):

IN: 2.34969592094s

OR: 5.83781504631s

Update:
(I don’t have the source code for the original test, as it was 6 years ago, though it returns a result in the same range as this test)

In request for some sample code to test this, here is the simplest possible use case. Using Eloquent for syntax simplicity, raw SQL equivalent executes the same.

$t = microtime(true); 
for($i=0; $i<10000; $i++):
$q = DB::table('users')->where('id',1)
    ->orWhere('id',2)
    ->orWhere('id',3)
    ->orWhere('id',4)
    ->orWhere('id',5)
    ->orWhere('id',6)
    ->orWhere('id',7)
    ->orWhere('id',8)
    ->orWhere('id',9)
    ->orWhere('id',10)
    ->orWhere('id',11)
    ->orWhere('id',12)
    ->orWhere('id',13)
    ->orWhere('id',14)
    ->orWhere('id',15)
    ->orWhere('id',16)
    ->orWhere('id',17)
    ->orWhere('id',18)
    ->orWhere('id',19)
    ->orWhere('id',20)->get();
endfor;
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080514.3635
1482080517.3713
3.0078368186951 

$t = microtime(true); 
for($i=0; $i<10000; $i++): 
$q = DB::table('users')->whereIn('id',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get(); 
endfor; 
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080534.0185
1482080536.178
2.1595389842987

More Related Contents:

  1. Storing JSON in database vs. having a new column for each key
  2. SQL Performance UNION vs OR
  3. MySQL “IN” operator performance on (large?) number of values
  4. What is better in MYSQL count(*) or count(1)?
  5. MySql views performance [closed]
  6. “SELECT COUNT(*)” is slow, even with where clause
  7. MySQL: Inner join vs Where [duplicate]
  8. Does Order of Fields of Multi-Column Index in MySQL Matter
  9. How to speed up sql queries ? Indexes?
  10. need to return two sets of data with two different where clauses
  11. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  12. MySQL – Operand should contain 1 column(s)
  13. How to get the difference in years from two different dates?
  14. Restore table structure from frm and ibd files
  15. mysql pivot/crosstab query
  16. MySQL: Split comma separated list into multiple rows
  17. UNION query with codeigniter’s active record pattern
  18. Mysql Convert Column to row (Pivot table )
  19. Remove Primary Key in MySQL
  20. Get most recent row for given ID
  21. Mysql covering vs composite vs column index
  22. Mysql insert random datetime in a given datetime range
  23. Greatest value of multiple columns with column name?
  24. MySQL UPDATE syntax with multiple tables using WHERE clause
  25. MySQL load data infile – acceleration?
  26. Calculating time difference between 2 dates in minutes
  27. mysql – Dynamic column alias
  28. How to select non “unique” rows
  29. Generate serial number in mysql query
  30. What are the benefits of only_full_group_by mode?

Leave a Comment