Why is primary key id and priority for SQL transport table?

[kendallb]

Looking over the SQL server code extensively as I port it to MySqlConnector, I noticed something odd. The primary key on the transport table is (id, priority), but the id column is an auto increment column so any time an entry is inserted into the database it is always going to have a unique id column, and hence it would be impossible to insert two values into the same with the same priority?

So I am wondering if the primary key should really just be the id column as the priority column is only used to determine ordering of events that get pulled out of the message queue by highest priority first, then by ascending visibility and then by ascending id. That might help with some transaction locks I have with the MySQL version, so was curious if there was a specific reason it was done that way in the SQL Server transport?

[mookid8000]

The original reason for doing this was to ensure that the priority was also part of the clustered index, assuming that that would make queries by priority faster. Don't know if that's the case, though... also, it seemd there's a nonclustered index idx_receive that includes all of the relevant fields, which I bet is the one used in the receive query.

So... simply using the auto-increment bigint id field as PK should be fine 🙂

[kendallb]

Yes, that is certainly what I found when debugging the MySQL code and sorting out the indexing.