Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is query NOT using my index?

Tags:

sql-server

CREATE TABLE [LB].[Orders]
(
    [OrderID] [bigint] IDENTITY(1,1) NOT NULL,
    [OrderDate] [date] NOT NULL,
    [Status] [nvarchar](max) NULL,

    CONSTRAINT [PK_MasterOrderID] 
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
) 

CREATE NONCLUSTERED INDEX [PK_Index]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate]); 

CREATE UNIQUE NONCLUSTERED INDEX [IX_OrderID_OrderDate]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate],[OrderID]); 

I am trying to speed up this query:

SELECT * FROM [BTP_NYA].[XX].[Orders] WHERE [OrderDate] = '20170921' AND [OrderID] = 62192

Why does this query NOT USE my IX_OrderID_OrderDate Index? How else can I make is faster?

enter image description here

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4232.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0032831" StatementText="SELECT * FROM [BTP_NYA].[LB].[Orders] WHERE [OrderDate]=@1 AND [OrderID]=@2" StatementType="SELECT" QueryHash="0x547011333B745205" QueryPlanHash="0xD16E72141B86DC03" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="144">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="836385" EstimatedPagesCached="209096" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="12369" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="59123">
              <OutputList>
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Index="[PK_MasterOrderID]" IndexKind="Clustered" Storage="RowStore" />
                <SeekPredicates>
                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@2],0)">
                            <Convert DataType="bigint" Style="0" Implicit="true">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="@2" />
                                </Identifier>
                              </ScalarOperator>
                            </Convert>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
                <Predicate>
                  <ScalarOperator ScalarString="[BTP_NYA].[LB].[Orders].[OrderDate]=CONVERT_IMPLICIT(date,[@1],0)">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Convert DataType="date" Style="0" Implicit="true">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="@1" />
                            </Identifier>
                          </ScalarOperator>
                        </Convert>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@2" ParameterCompiledValue="(62192)" ParameterRuntimeValue="(62192)" />
              <ColumnReference Column="@1" ParameterCompiledValue="'20170921'" ParameterRuntimeValue="'20170921'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
like image 932
ManInMoon Avatar asked Sep 21 '17 11:09

ManInMoon


People also ask

How do I force an index in SQL query?

FORCE INDEX works by only considering the given indexes (like with USE_INDEX) but in addition it tells the optimizer to regard a table scan as something very expensive. However if none of the 'forced' indexes can be used, then a table scan will be used anyway.

Why index is not being used in MySQL?

The Benefits and Drawbacks of Using Indexes in MySQLIndexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries – when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time.

Does index work with in query?

The IN clause becomes an equality condition for each of the list and will use an index if appropriate.

Why SQL Server is using wrong index?

The reason SQL Server ignores that index is that it's useless to it. The index is first sorted by symbolID , and you don't want a specific symbolID, but a bunch of random values. This means that it has to read all over the table.


2 Answers

The reason the non-clustered index is not used is because it is more efficient to select the single row using the unique primary key clustered index. You can't get any faster than that to select all columns for a single row (barring a hash index on an in-memory table).

like image 158
Dan Guzman Avatar answered Sep 21 '22 04:09

Dan Guzman


It does a clustered index seek on PK_MasterOrderID.

This is because you do select * and IX_OrderID_OrderDate doesn't include any columns.

If you would create the index with

CREATE UNIQUE NONCLUSTERED INDEX [IX_OrderID_OrderDate]  
ON [BTP_NYA].[LB].[Orders] ([OrderDate],[OrderID]) INCLUDE([all], [selected], [columns]); 

, it would be used.

like image 22
Raphael Müllner Avatar answered Sep 18 '22 04:09

Raphael Müllner