3

I think I have found the answer for this already but I am hoping to get some additional perspective.

Assume we are JOINing two tables together on a shared column and each table then has a different column we are going to do a Constant search on. When we build an index to support the query, for each table do we want to put the JOINing column first, or the Constant column first? I am thinking now it is the Constant column first. When I look at a query plan for a different query that prompted this question, it appears it tries to create a subset of each table and then JOIN them together. Instead of JOINing the two tables togeather and filtering down from there.

EX: Joining Shipments to Customers where Shipment is Shipped and Customer is Active

SELECT [Columns]
FROM Shipment S
   INNER JOIN Customer C
      ON S.CustomerID = C.CustomerID
WHERE S.IsShipped = 1
AND C.IsActive = 1

I am thinking the two best indexes to use are below. Because the Query Optimizer would prefer to scan the Constant first then JOIN on the 2nd column instead of JOINing the two tables together and filtering on the constant after that.

CREATE NONCLUSTERED INDEX [IX_IsActive-CustomerID] ON [dbo].[Customer]
(
    [IsActive] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_IsShipped-CustomerID] ON [dbo].[Shipment]
(
    [IsShipped] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Instead of:

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsActive] ON [dbo].[Customer]
(
    [CustomerID] ASC,
    [IsActive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsShipped] ON [dbo].[Shipment]
(
    [CustomerID] ASC,
    [IsShipped] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Is that right?

3

Assuming there are no other queries of concern, the general answer is that you want more selective criteria in the leading columns of the index (note that selectivity here is in relation to the predicate rather than a measure of how unique the particular values in the column are). In general, you want the optimizer to eliminate as many rows as possible as quickly as possible.

Assuming that CustomerID in Shipment is NOT NULL and has a foreign key to Customer, meaning that the inner join is guaranteed not to eliminate any rows from Supplier, then s.IsShipped = 1 is the only selective predicate and it would make sense for it to be the leading column. If, on the other hand, the inner join was more selective (imagine that someone moved rows from Customer to Customer_Archive periodically and Shipment could join to either of those tables), it would make sense to have CustomerID as the leading column.

  • For the purpose of this question I am more trying to understand some of the under-workings of the query analyzer. If we assume that 98% of the records in Shipment have IsShipped = 1 and 98% of records in Customer have IsActive = 1. That would mean CustomerID is more selective by a lot. – Kirk Saunders Oct 29 '19 at 19:41
  • 1
    @KirkSaunders - CustomerID would be more selective if fewer than 98% of the rows in Shipment had a matching CustomerID in Customer. The most selective predicate is the one that eliminates the largest fraction of rows. If half the rows get eliminated via the join, you'd generally want the join to happen first and the CustomerID to be the leading column. If doing the join eliminates 0 rows, you'd want IsShipped and IsActive to be the leading columns. – Justin Cave Oct 29 '19 at 19:46
  • 1
    When I look a query plans (for a different query that prompted this question) it appears to me the query analyzer tries to create the subset of each table (SELECT [Columns] FROM Shipment WHERE IsShipped = 1 and SELECT [Columns] FROM Customer WHERE IsActive = 1) and JOIN them together after that. Presumably an index leading with CustomerID can't be used for generating those two subsets, but could be used for the JOIN. Where an index leading with the "flag" column would be able to be used for building the Subset and for the JOIN. Does that make sense or am I off? – Kirk Saunders Oct 29 '19 at 19:46
  • 1
    @KirkSaunders - Yes, it sounds like the optimizer expects the IsActive and IsShipped predicates to be more selective than the join so it is executing them first. That seems reasonable given these two tables (and making reasonable assumptions about the data). Given a different pair of tables where the join eliminated a substantial fraction of rows, it would likely make more sense to do the join first and then filter. – Justin Cave Oct 29 '19 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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