Key Lookup Threshold Part 2
Grant Fritchey(@gfritchey) posed this question to me on twitter after my posted on the key lookup threshold post.
@statisticsio Interesting post. Do you think that threshold is dependent on the data involved? Or maybe on the size of the key?8:28 AM Feb 5th from TwitterGadget in reply to statisticsio
I kind of assumed so which is why I did char(1000). However, I have just tested with char(1). The numbers are interesting. Please refer to the original post for repro code.
| IO | IO | |
| Rows | CHAR(1000) | CHAR(1) |
| Scan | 422 | 9 |
| 1 row | 4 | 4 |
| 5 rows | 12 | 12 |
| 10 rows | 22 | 22 |
| 25 rows | 52 | 52 |
| 50 rows | 114 | 102 |
| 100 rows | 217 | 202 |
| 250 Rows | 526 | 502 |
| 500 Rows | 1043 | 1003 |
| 1000 Rows | 4141 | 4007 |
The threshold is crossed much earlier because the scan is smaller. This is a small table especially with CHAR(1) so take this test with a grain of salt. Run your own tests when you are working with large production data.
I would also venture to guess that as the complexity of the query increases the threshold gets lower especially on more complex queries like when grouping especially when grouping and outer joining.
A covering index is probably the best solution in most cases unless you need to touch all rows even then, it might be better,.
