Mysql equal vs greater than(less than) performance difference

Is there a performance difference in Mysql for big dataset while using equals or greater than in where clause.

I can run following two queries for same purpose.

select * from 开发者_如何学JAVAMy_Table where MY_Field > 100; // 100 is old value

or

select * from MY_Table where MY_Field = 101; 

It is known value of My_Field if greater than 100 will 101.

Any Suggestions?

---

If MY_Field is covered with index then the difference in searching values in index is not big. This is true until the result set is about 30% of the total number rows in a table.

But, for each record found in index mysql need to perform seek on data file to find the appropriate row (since you have SELECT *).

So - in your case if you know the specific field value it must be better to use =.

---

Depends on the index type on this particular column: BTREE is good when you search in ranges, HASH is good when you search for equation

---

You can optimize query if you know only one result is going to be fetched use LIMIT 1

select * from MY_Table where MY_Field = 101 LIMIT 1;

which is better as while executing if it finds a single row matching result mysql will stop execution

---

Better use MY_Field = 101 because mysql will perform ref access type which means index will be compared with reference value, which is faster that range in case MY_Field > 100 which means mysql will compare range values in index.

You can check it in type field in explain for your query.