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.3k 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

Related questions

0 votes
1 answer
0 votes
1 answer
asked Oct 7, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
asked Oct 18, 2019 in Web Technology by Sammy (47.8k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...