Does the same index on two identical tables with same number of rows but the second table with more columns holding more data affect the lookup?

Question

Let’s say there are two tables table1 and table2 where table1 has 4 columns and table2 has 10 columns, but the first 4 columns of table2 are same as table1. The second column in both the tables say column2 is indexed. Will the lookup speeds be same on both the tables when searching by the index, or will a lookup on the table having lesser column count (table1) be faster?

Answer 1

An index lookup is typically used when the where clause looks like:

where column2 = <some value>

In this case, the number of columns in the table -- the size of the row should have almost no affect on the performance. The way such a query works is that the index is used to identify the rows that need to be returned. This phase of the operation only uses the index, and it only contains the column values and row ids.

Once the rows are identified, the engine needs to look them up on the data pages. If you have only one row, then the the size of the records makes no difference. Only the data pages for that record are looked up (okay, there could be some lookahead reads as well, but that would be the same as well).

The only potential difference is when multiple rows match the condition. The smaller the rows, the more likely they are to be on a single data page. However, if your table is large and only a handful of rows are selected, then they are likely on different pages anyway.

There is a rare situation that can occur if the table is bigger than available memory and you are retrieving a lot of rows from the table. This is called thrashing and it is more likely to occur with wider rows (because they occupy more space).

So, the size of a row basically has no effect on relative performance, other than the overhead of bringing back additional rows if you have them.