Comment by Kinrany
17 days ago
One query isn't cheaper than two queries that do the same amount of IO and processing and operate in the same memory space
17 days ago
One query isn't cheaper than two queries that do the same amount of IO and processing and operate in the same memory space
How is it the same IO?
Each query needs to navigate the index then read. The two queries do that twice.
Is it faster to read pages 30-50 of a book by:
a) Go to page 30, read until 50
b) Go to page 30, read that page, close book, open book, go to page 31 and so on.
Each page open you get to binary search to find the page.
It needs to read the index twice, sure, but that's also likely to be cached? Guessing though.
Yes, (index) scans are rarely faster typical web apps.
Unless you have toy amounts data... or doing batch operations which is not typical (and can be problematic for other transactions due to locking, etc...)
I admit it is rare. It is more likely if the app has search and DB has been optimised to bring the needed retrevied data onto the index. But it isn't like I haven't reached for a clustered index a few times.