Field orer in compound index for lookup?

[Kjell Rilbe]

Hi,

Assume we have a table with many records. It has two bigint columns:

TenantId - identidifes which system tenant the record belongs to. Not so
selective.
Id - globally unique id number for this particular record.

I would normally use only Id for primary key, but because of some quirks
in Entity Framework we need to include TenantId in the table's primary key.

Similar pairs of columns will also appear sometimes not as primary key,
but indexed and referenced in foreign keys in other tables.

Will index lookups perform best with (TenantId, Id) or with (Id, TenantId).

I would guess the latter, since Id has a lot better selectivity.

Mvh,
Kjell

[Dimitry Sibiryakov]

Kjell Rilbe wrote 24.11.2021 17:03:
> Will index lookups perform best with (TenantId, Id) or with (Id, TenantId).

Former index is better because of index key compression.

> I would guess the latter, since Id has a lot better selectivity.

Comparison is not sequential, index keys are compared as whole, so there is
no difference for two bigints.

--
WBR, SD.

[Mark Rotteveel]

It would be better to use (Id, TenantId) as it will more quickly
pinpoint the record. It will also make queries that only use the Id
faster.

I know Dimitry suggested the reverse because of index compression, but I
think those benefits are smaller than the benefit of the improve lookup
speed.

Mark

[Kjell Rilbe]

That was my initial thought too. But it would more or less double the
index size, considering TenantId part will never be compressed, but
duplicated for every single index entry. Double index size would mena
roughly double the I/O, albeit cache would possibly make it mostly
in-memory.

I guess only real-world tests would actually be conclusive.

Regards,
Kjell