Greater than vs in condition mysql performance

Question

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

First you have to add an index on status. – majidarif Mar 15 '14 at 06:59 — majidarif, Mar 15 '14 at 06:59
@majimboo, thanks, forgot to mention it, updated – dav Mar 15 '14 at 07:01 — dav, Mar 15 '14 at 07:01

majidarif · Answer 1 · 2014-03-15T07:15:54.313

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

score 0 · Answer 2 · answered Mar 18 '14 at 17:39

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.

Greater than vs in condition mysql performance

2 Answers2