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?
<?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>
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.
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.
The IN clause becomes an equality condition for each of the list and will use an index if appropriate.
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.
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With