0

I have a simple SELECT query:

SELECT * FROM MyTable
WHERE ParentId IN (0, 1) OR ParentId IS NULL
ORDER BY SortColumn
LIMIT 0, 2048;

Why is it that this query runs 10x slower when I write WHERE ParentId IN (1) instead of (0, 1)?

The query runs faster whether it finds the second id or not.

Edit:

It looks like running the query checking for 1 element and without the OR ParentId IS NULL runs just as fast as running the query checking for multiple elements.

ParentId is a foreign key to another column and therefore indexed.

Why would this matter so much?

Charles W
  • 2,262
  • 3
  • 25
  • 38
  • What did your test look like? How many elements do you have? – Shahar Jan 13 '15 at 19:27
  • What does the execution plan tell you? – Rick S Jan 13 '15 at 19:27
  • @Shahar There are over 500,000 elements in the table. 6 columns, 3 foreign keys. And an index on the `varbinary(767) SortColumn` – Charles W Jan 13 '15 at 19:28
  • @RickS It looks like when it's checking multiple elements it does an Order with Filesort = false. When there is only 1 element, it does a Key Lookup instead of a Full Index Scan, It is very likely however that there will be a lot of rows returned. – Charles W Jan 13 '15 at 19:39
  • similar question: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance which shows that direct comparison is not necessarily faster, although the OP questions a single value rather than multiple via OR. – zamnuts Jan 13 '15 at 19:40

0 Answers0