SQL: Index Scan with Row Lookup via /r/learnprogramming


SQL: Index Scan with Row Lookup

I found this explanation:

Index Scans with Row Lookups: With no index that can be directly used for a seek is found but an index containing the right columns is present an index scan may be used. For instance, if you have a large table with 20 columns with an index on column1,col2,col3 and you issue SELECT col4 FROM exampletable WHERE col2=616, in this case scanning the index to query col2 is better than scanning the whole table. Once matching rows are found then the data pages need to be read to pickup col4 for output (or further joining) which is what the "bookmark lookup" stage is when you see it in query plans.

Index Scans without Row Lookups/covering index: If the above example was SELECT col1, col2, col3 FROM exampletable WHERE col2=616 then the extra effort to read data pages is not needed: once index rows matching col2=616 are found all the requested data is known. This is why you sometimes see columns that will never be searched on, but are likely to be requested for output, added to the end of indexes – it can save row lookups. When adding columns to an index for this reason and this reason only, add them with the INCLUDE clause to tell the engine that it doesn't need to optimise index layout for querying based on these columns (this can speed up updates made to those columns). Index scans can result from queries with no filtering clauses too: SELECT col2 FROM exampletable will scan this example index instead of the table pages.

My question is, for the example of an index on (col1, col2, col3), and doing something like SELECT col4 FROM table WHERE col2=616, why does it use the index?

Isn't this like, searching a phonebook sorted by last name, then firstname and trying to find all the people named Michael? How is it better than a table scan?

Submitted July 15, 2017 at 04:40PM by Hufe
via reddit http://ift.tt/2v4PCvG

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s