-1

I want to order the records in my tasks table according to completed and then according to workOrderID, just like I can to with the ORDER BY clause of a SELECT query, so that this

taskID workOrderID completed
1 1 t
2 1 f
3 3 t
4 2 t
5 9 t
6 4 f
7 5 f
8 4 t
9 1 f

is stored like this

taskID workOrderID completed
1 1 t
4 2 t
3 3 t
8 4 t
5 9 t
2 1 f
9 1 f
6 4 f
7 5 f

When I try, I get a syntax error, and I don't see that functionality mentioned in the docs. I think this could speed up some queries.

3
  • 3
    Any reason why you don't want to use a compound index for that? And why do you think clustering the table the way you want is useful? This sounds suspiciously like an X-Y Problem™.
    – mustaccio
    Feb 9 at 22:15
  • 1
    Why can't you just do an index (completed DESC, workOrderID, taskID) Feb 10 at 18:59
  • When I try - when you try what, exactly? There's no reason why you can't create an index on (completed, workOrderID) and CLUSTER on that? I'm VtC but will vote to Reopen if you provide the answers to the questions you've been asked.
    – Vérace
    Feb 28 at 10:50

1 Answer 1

1

CLUSTER physically rewrites a table (and consequently all its indexes) according to the sort order in one given (or saved) index. Not multiple.
Consider a multicolumn index for your use case.

The rewritten table is in pristine condition, all bloat is removed, and wiggle room per page according to the FILLFACTOR setting is restored. So, after re-populating the cache, you'll see a performance improvement if the table (and/or indexes) had been bloated, independent of the sort order. Keep this side-effect in mind.

The sort order itself helps queries for which data locality has been improved (and/or which need that exact sort order), but not others (which may suffer). The effect deteriorates over time with writes to the table. See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.