Is query efficiency affected by data not relevant to the query?

Question

Say I have roughly 200 million records and have an indexed column user_id on a product table. If I select back 1 million records by doing the following query :

• select * from product where user_id = 1 (Assuming my user_id is indexed.)

Would the other 199 million rows' column data affect the efficiency of my query ?

Example :

1. Consider 199 million rows were completely populated (containing blobs / varchars)
2. Consider 199 million rows were only partially populated, and carried a lot of null values

I've always understood this as the query would perform the same in either scenario - since user_id is indexed and could bypass traversing through all the data - but I'd like to get confirmation on this.

If there's another question / resource that answers this - feel free to redirect me elsewhere. Just looking for some confirmation (and an explanation if my assumption incorrect).

Thanks.

Answer 1

Summary: It depends on

• Percentage of rows being fetch (at least according to imprecise statistics)
• Size of buffer_pool (relative to size of table)
• ENGINE
• How much off-record blobs, etc, you have.
• Whether the data is somewhat ordered by user_id.

Details:

For smaller numbers, I would simply say "No". But you describe a very large table -- possibly large enough to have issues with caching.

Let me start by explaining a few things...

• user_id = 1 is a tiny percentage of the table, so it is very likely that INDEX(user_id) will be used.
• I am assuming you are using InnoDB.
• The lookup will work this way: Reach into the index for the first index row with user_id = 1; scan forward until that no longer holds true (1M rows as you say). For each of those index rows, reach into the data BTree to get * (using the PRIMARY KEY).
• If the data is not effectively sorted by user_id, that will be upwards of 1M lookups in the data. InnoDB blocks are 16KB (by default). So this might involve 16GB of data being loaded.
• You mention blobs, etc. They are probably stored 'off record', that is, in some other block. Maybe another 16GB to be fetched? If you don't need the blobs, don't say SELECT *, specify only what you want -- this is an example of a significant speedup.
• If the other rows are full of NULLs, then that changes how many rows are packed in each 16KB block. This may (but probably won't) mean that you won't need a full 1M blocks for the desired rows. That is, the caching in the buffer_pool may be useful.
• What is the value of innodb_buffer_pool_size? If it is much less than 32GB, then there will be a lot of I/O. If it a not more than 32GB, then a second run of that same query will find everything in cache, and run a lot faster.
• If it weren't a "tiny percentage", but instead over about 20%, the Optimizer would probably decide that a table scan is cheaper than bouncing back and forth between the index's BTree and the data BTree.

(The EXPLAIN will point out whether the index will be used, but not the rest of the details.)

Answer 2

select * from product where user_id = 1

Actually several data pages will hold 200 million records beside blobs.

and Rest of 199 million record will definetly affect the select query no matter whether UserID is CI or NCI

Assuming USERID is Nonclustered index.First userid will be located in leaf level,then if table have another CI then it will point to Root level then rest of the column will be retrive.

So optimizer have to read across several data pages.Here BLOB do not matter but those varchar column matter.More heavier the varchar column,more the data pages.

In Fact 200 million is lot,so even if you have all int column even then there will be lot of data pages.

Also in this example Selectivity of index USER ID is less so may be optimizer decide to Index Scan instead of Index seek.

If USERID is CI then there will be one less step .Optmizer will directly locate All root level .

what if USERid is spread across several data pages ?

So again ther is danger of Index scan instead of index seek.

So instead write,

select col1,col2, onlyrequirecolumn from product where user_id = 1

and create covering index on col1,col2, onlyrequirecolumn. So query will be little faster.

Also 200 million record table need partitioing.

Also read,

BLOB data

Row

Index (CI+NCI+covering index)