CODEWITHSUNDEEP
mysqlsqlperformance
tezz
tezz

Reputation: 349

Does MySQL 'OR' and two SELECT statements have same complexity?

1. "SELECT id, name FROM Customers WHERE id = id1 OR id = id2"

2. "SELECT id, name FROM Customers WHERE id = id1" and again, "SELECT id, name FROM Customers WHERE id = id2"

Does it take same time for the above two cases in the context of MySQL? OR How does OR gets implemented internally?

EDIT:

Assume Customerstable is indexed on id column

Upvotes: 1

Views: 179

Answers (2)

Rick James
Rick James

Reputation: 142298

90% of a simple select is overhead (network, parsing, optimizing, etc).

id = 123 OR id = 345 is optimized to id IN (123, 345). If there is an index on id (the PRIMARY KEY is an index), this is 2 probes into the table.

So if fetching one row is 100% of effort, then fetching 2 is about 110% of that (90% overhead, then 2 units of real work).

Meanwhile, 2 separate selects would be 200%.

On the other hand, if you have

WHERE x = 98 OR y = 65

there is no good indexing method for such.

Plan A: A full table scan, checking x and y for every row. Really slow for a big table.

Plan B: Change it to

( SELECT ... WHERE x = 98 )
UNION
( SELECT ... WHERE y = 65 )

This will be more like 200%. That is, making two queries helps in this case.

Plan C is "index merge union", which the Optimizer only occasionally resorts to.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You should phrase this as:

SELECT id, name
FROM Customers
WHERE id IN (@id1, @id2);

Assuming that id1 and id2 are passed into the query, then they would only be similar if you had an index on id (which is reasonable). Of course, running two queries incurs overhead of running two queries. However, the complexity is the same, from the perspective of algorithmic analysis.

Without an index, the two-query version is slower by a factor of two, because it needs to scan the table twice. However, although that is slower, it does not affect complexity.

Upvotes: 1

Related Questions