MYSQL IN or multiple conditionals

Question

I have a simple query which is returning records based on the field status not having certain values. Lets say for arguments sake that the field can have values 1,2,3...10 and I want to return all records that don't have values 3, 7 and 9. Which of the following would be best to use?

Option 1.

SELECT `id` FROM `tbl` WHERE (`status` != '3' AND `status` != '7' AND `_status` != '9')

Option 2.

SELECT `id` FROM `tbl` WHERE `status` NOT IN ('3','7','9');

Thanks for you help.

Answer 1

I suspect the optimiser would reduce both queries to the same thing, and if that is true I prefer the second option, as it's shorter and thus easier to parse.

You should try running EXPLAIN on each query with real data to confirm.

Answer 2

Best way to optimize your Query using EXPLAIN Command for Example

EXPLAIN SELECT id FROM tbl WHERE status NOT IN ('3','7','9'); and see the execution time