MYSQL OR vs IN performance

Question

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.

Answer 1

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