4 ms·
Try adding a lot of data to the table; with different kinds of correlation between questionId and participantId. (i.e., many questionIds per participantId, many
by doty 17y ago
Try adding a lot of data to the table; with different kinds of correlation between questionId and participantId. (i.e., many questionIds per participantId, many participantIds per questionId, and both.)
A databases query optimizer attempts to guess whether going through the index would actually require less work than going through each row. Remember, unless you're the special "rows are in this order" index, there's an extra set of disk seeks involved in following an index. If you thought that going through the index would result in you visiting all the rows anyway, then you should just avoid the index and go through all the rows. More specifically, if going through the index does not eliminate the need to visit enough of the rows, then it should have just gone through all of the rows to begin with.
- kevingadd 17y agoI don't know if it's meaningful to call the query a table scan when the database engine intelligently guesses that it's faster to scan the table than run through the index. Usually when you're talking about table scans in the context of optimization, it's queries that must be a table scan, no? I'm sure there are lots of cases where the database engine's heuristics can be wrong, or an index-based scan is less efficient than a full table scan, but the source article isn't explicitly talking about any of those cases.