CODEWITHSUNDEEP
mysqlperformance
dav
dav

Reputation: 9267

Greater than vs in condition mysql performance

I have posts table, post's status is a tinyint, can be a number between 1-10. I need to select those posts, that have status either of these numbers - 6, 7, 8, 9, 10. So, what solution will be better in terms of performance

1) SELECT id, title, status FROM posts WHERE status > 5

2) SELECT id, title, status FROM posts WHERE status IN(6, 7, 8, 9, 10)

I did not list using OR, as from this answer IN is faster

MYSQL OR vs IN performance

Thanks

UPDATE

status column is indexed

Upvotes: 0

Views: 367

Answers (2)

SCI
SCI

Reputation: 555

I'd expect the IN-clause to be slightly faster, because it can use the given values as direct keys to the index and thus to the rows.

Using GT will have at least one additional operation to determine all of that keys.

But majimboos approach is generous: Use EXPLAIN to see what will happen in a concrete case.

Upvotes: 0

majidarif
majidarif

Reputation: 20005

See this fiddle: http://sqlfiddle.com/#!2/346380/3

Select <: 

Record Count: 2; Execution Time: 27ms

Select < explain:

ID  SELECT_TYPE TABLE   TYPE    POSSIBLE_KEYS   KEY KEY_LEN REF ROWS    EXTRA
1   SIMPLE  supportContacts range   helper  helper  5   (null)  2   Using where

Select IN:

Record Count: 2; Execution Time: 1ms

Select IN explain:

ID  SELECT_TYPE TABLE   TYPE    POSSIBLE_KEYS   KEY KEY_LEN REF ROWS    EXTRA
1   SIMPLE  supportContacts ref helper  helper  5   const   2   Using where

As most say TYPE ref would be faster than range. So I believe the latter will be more faster.

This should be a good read: Write better SQL queries with Explain

Upvotes: 1

Related Questions