The Heap™ – Consultancy ©®

« first day (3716 days earlier) ← previous day next day → last day (1138 days later) »

John K. N.

6:40 AM

Good Morning

2 hours later…

McNets

8:10 AM

Morning

Tom V

8:51 AM

Morning

5 hours later…

Tom V

1:53 PM

crickets

@HannahVernon I see your stackexchange profile page still links to your previous twitter and github profile

ypercubeᵀᴹ

2:51 PM

@ypercubeᵀᴹ I understand what you're saying. The exact query was SELECT ColumnA, ColumnB, ColumnC FROM Table1 WHERE ColumnA = 123 AND ColumnB = 987. The execution plans were the same, minus the difference in which index was used to seek on. The table itself was in the billions of records, and the predicates above probably returned millions of records (if I recall correctly). Again, I understand it's not a common case but in the example above the difference was of measurable seconds after testing both ways multiple times. The hardware of the server was modest though (e.g. 16 GB of RAM). — J.D. 2 hours ago

@PaulWhite the above (alleged) behaviour of index (a,b) behaving badly comparing to index (b,a). ^^ Is it plausible?

Not a bug but it sounds like a not so well optimized edge case of the plan executor, if it happens.

Unless statistics or index bloat or any other detail was causing it.

Cade Roux

3:10 PM

That point about most selective first makes little sense.

Putting thread id before user is going to mean that index is useless for finding by user.

bbaird

I'm not an internals guy buy any means, but it doesn't pass the smell test. I can maybe imagine having to read an extra page here or there in the b-tree and maybe that multiplies if you're doing a billion seeks, but that's a big if.

Cade Roux

If you are always seeking with equality on both columns, I don't think the order is gonna matter. Even if the statistics/plan is not accurate, it's a seek on the whole key.