Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in SQL by (20.3k points)

I am wondering if there is any difference in regards to performance between the following

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)

SELECT ... FROM ... WHERE someFIELD between  0 AND 5

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... 

or will MySQL optimize the SQL in the same way compilers will optimize code?

EDIT: Changed the AND's to OR for the reason stated in the comments.

1 Answer

0 votes
by (40.4k points)

Performance-wise if you see, In is much faster than OR operator.

If you are running a loop of 1000x the equivalent queries (for consistency, if you used sql_no_cache):

IN: 2.34969592094s

OR: 5.83781504631s

And if you are looking for some sample code to test this, then here's the simplest possible use case. 

Raw SQL equivalent gets executed in the same manner if you are using Eloquent for syntax simplicity like this:

$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

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.2k users

Browse Categories

...