Mysql equals vs more (less) performance difference

Question

Is there any performance difference in Mysql for a large dataset when using equal or greater than where where.

I can execute the following two queries for the same purpose.

select * from My_Table where MY_Field > 100; // 100 - old value

or

 select * from MY_Table where MY_Field = 101; 

The value of My_Field is known, if more than 100 will be 101.

Any suggestions?

Answer 1

If MY_Field covered by an index, the difference in search values ​​in the index is small. This is true until the result set is about 30% of the total number of rows in the table.

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

So - in your case, if you know the specific value of the field, it is better to use = .

Answer 2

Depends on the type of index in this particular column: BTREE is good at searching in ranges, HASH is good at looking for an equation

Answer 3

You can optimize the query if you know that only one result will be obtained using LIMIT 1

 select * from MY_Table where MY_Field = 101 LIMIT 1; 

which is better than execution, if it finds a result with a single line, mysql will stop execution

Answer 4

It is better to use MY_Field = 101 , because mysql will execute the ref access type, which means that the index will be compared with the reference value, which is faster than range in the case of MY_Field > 100 , which means that mysql will compare the range values ​​in the index.

You can check it in the type field in explain for your request.