Why full index scan over full table scan?

Question

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?

Answer 1

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.

Answer 2

Database engine is having its own optimizer.So while parsing and executing any query it evaluates it on cost based optimization.Upon comparison basis on resources utilization,system choose path which results best.

Answer 3

In this case, it is always better to do an index scan instead of a table scan. All of the needed columns are in the index; that is, it is a "covering" index.

The table has lots of columns, correct? So a row in the table is rather wide.

The index has two columns -- email the PRIMARY KEY, cid (assuming you are talking about InnoDB). That makes for a much narrower 'row'.

An index and the data have essentially identical BTree structures. Scanning the index is faster because less data is handled.