Does order of items in an SQL: WHERE IN () matter?

Question

Does the order of the values in a WHERE IN clause matter? (this is on Firebird SQL if that is important)

Such as does:

where field1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

offer a performance increase over

where field1 in (3, 5, 10, 2, 1, 8, 6, 9, 4, 7)

Swords

I removed the delphi tag, because your question has absolutely nothing to do with Delphi. Please use only tags that actually apply to your question; the purpose of tags is to classify questions into categories. Thanks. – Ewald

My guess would be that unless the IN-list is huge it makes no noticeable difference on modern hardware, because at the end of the day there has to be a for-loop there which will iterate through elements of that list and compare them to the field value. – Surmount

Thanks was not sure if the for loop caused unnecessary index traversal in a manner that mattered or if it sorted the list anyway. Thx – Swords

Answer 1

Yes it will, but possibly in an implementation dependent manner. The IN is processed as sequential OR's, and one might assume that the most likely processing order is as written.

That said, I would start by assuming that the optimizer will process the elements in the order given, because that is easiest, and rank them in the set from most- to least-likely. It can't hurt, and will most probably help. Whether the difference is measurable or significant is another matter; measure it and let us know.

Chrotoem

i was not sure if it would force the index to move back and forth traversing areas it already checked (like a fragmented hard drive) or if it sorted first on its own anyway so I didn't need to spend time sorting as well. I will run tests to find out. – Swords

Seems to have no noticeable difference, or difference was too small to measure in the tests I did. – Swords

@ChrisValentine: Not surprising. If the set got large, I would put it in a lookup table, add a clustered index, then inner-join to it. I believe SQL optimizers can always be expected to perform such a join more efficiently than an OR. The OR function, after all, is simply syntactic sugar for a set too small to bother putting in a table. – Chrotoem