CODEWITHSUNDEEP
mysqlindexing
John
John

Reputation: 10156

Question about using or in mysql query

I wanted to get some clarification, if using OR in a sql statement on indexes does it use the index or no?

For example if I have this query:

SELECT * FROM account_orders WHERE accountid = 1 OR accountid = 0

I have an index on the accountid field

Will mysql use the index? Or would it be better to use a union? I keep reading that mysql doesnt use an index with an or statement, or perhaps Im reading that incorrectly. Any help/explanation would be helpful. Using mysql 5.x

Upvotes: 0

Views: 308

Answers (3)

Bohemian
Bohemian

Reputation: 425418

Database won't use an index if you use OR like you have.

The work around is to rephrase it using IN. as follows:

SELECT * FROM account_orders WHERE accountid IN (1, 0)

Databases will use an index (if available) with this syntax

Upvotes: 1

tiny_mouse
tiny_mouse

Reputation: 489

This is somewhat off topic, but you could just do:

SELECT * FROM account_orders WHERE accountid IN (0,1)

Upvotes: 0

matsko
matsko

Reputation: 22223

Best to prefix your select statement with EXPLAIN. This will report about the result of the query and if any indices were used.

EXPLAIN SELECT * FROM account_orders WHERE accountid = 1 OR accountid = 0

Upvotes: 0

Related Questions