Mysql – Why full index scan over full table scan

indexMySQL

I have a query that pulls from small tables in MySQL:

SELECT C.cid, C.email, R.flight_number
FROM customer C INNER JOIN reservation R ON C.cid = R.cid
WHERE C.email LIKE '%d.com';

There exists an index in the customer table on the email column.

Based on what I've read on the subject, my thinking is that a full index scan would only slow down the query, and performing a full table scan and ignoring the index would be better than performing a full index scan in this case. However the visual plan shows that the query uses a full index scan over a full table scan. Why?

Best Answer

If the index contains both cid and email, there is no need to look at the records. All the data will be retrieved from the index. This is a common optimization.

The where clause should (on average) reduce the record count to less than four percent of the records. This should be a small enough set to make using the index faster than table scanning. Different engines have a different cost cutover point. Matching the wildcard on the index is likely faster than than matching against the record (column position matters).

In a case like this, the optimizer will be guessing data distribution. The optimizer likely doesn't have stats on a trailing match, so it will likely be guessing based on 5 random characters. This would severely tilt the query in favor of an index scan, as the expectation is that only a small (much less than 1) percentage of records will match.

Related Solutions

Sql-server – How to get rid of clustered index scan

The nested loop is happening at this part, where you're joining the a2_AssessmentQuestion to itself:

SELECT
   [Extent18].[ID] AS [ID]
FROM
   [dbo].[a2_AssessmentQuestion] AS [Extent17]
   INNER JOIN [dbo].[a2_AssessmentQuestion] AS [Extent18]
      ON [Extent17].[PartID] = [Extent18].[PartID]
WHERE
   [Extent1].[AssessmentQuestionID] = [Extent17].[ID]

The where condition is not applied at this part, as you can see from the whole query you posted.

Looking at the predicates and outputs of the nested loop and the clustered index scan, I suggest you try to create a nonclustered index on a2_AssessmentQuestion.PartID.

Nested loop details from the execution plan Clustered index scan details from the execution plan

Sql-server – Clustered Table Scan Because of “SELECT *”

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice:

Perform a table / clustered index scan (therefore all columns are there)
Perform a seek, then perform lookups to retrieve the columns not covered

Which way it will choose depends on a variety of things, including how narrow the index is, how many rows match the predicate, etc. You can force a seek with the FORCESEEK hint, but I suspect it will end up performing the same or worse than the scan SQL Server has chosen in your case.

Some options:

Change the app to run a proper query. I listed this first for a reason.
Create a view that selects only the columns you need:
```
CREATE VIEW dbo.myview
WITH SCHEMABINDING
AS
  SELECT col1, col2, col3 FROM dbo.tablename;
```
Then you can change the app to SELECT * from this view. Or you can get even more creative and rename the original table, and change the name of this view to what the name of the table used to be. Breaking change, obviously; proceed with caution.
Add all of the other columns to the key or INCLUDE list for the index. If these are hard-coded values and always the ones used, you may consider a filtered index.

Best Answer

Related Solutions

Sql-server – How to get rid of clustered index scan

Sql-server – Clustered Table Scan Because of “SELECT *”

Related Question