4 ms·
I can't imagine any halfway decent database engine failing to utilize at least one index. Maybe the author is thinking of cases where you have multi-column inde
by kevingadd 17y ago
I can't imagine any halfway decent database engine failing to utilize at least one index. Maybe the author is thinking of cases where you have multi-column indexes that are ordered incorrectly and as a result can't be used? I'm more inclined to suspect that they just don't know what they're talking about, though. It's pretty trivial to verify that any decent database engine will use one of your indices to optimize that query if possible.
The hack for detecting table scans seems like it would be super useful if using MSSQL but I'm inclined to be wary of such a solution to what appears to be a basic education problem.
It takes about 2 minutes to test this out in SQLite:
CREATE TABLE response (
text TEXT,
questionId INTEGER,
participantId INTEGER
);
CREATE INDEX i1 ON response (questionId);
CREATE INDEX i2 ON response (participantId);
EXPLAIN SELECT text FROM response WHERE questionId = 27 AND participantId = 38
The resulting bytecode clearly makes use of an index instead of performing a table scan. If you drop both indexes and explain the query again, the bytecode changes to indicate that it is performing a table scan.
- ergo98 17y ago>The resulting bytecode clearly makes use of an index instead of performing a table scan. If you drop both indexes and explain the query again, the bytecode changes to indicate that it is performing a table scan. You have no data. It knows that. It's showing you a query plan based upon no stats. http://www.sql-server-performance.com/articles/per/index_not_equal_p1.aspx http://www.sql-server-performance.com/articles/per/index_not... Fairly true across different RDBMS products.
- kevingadd 17y agoYes, but the original post makes no mention of things like statistics. It states a general principle that is, in very many cases, not true. Of course if the database engine has statistics on the table it can choose not to use the index, the point is that I don't know of any database engine that isn't able to use the index.
- hackermom 17y ago<?php $entries = 500000; $db = new pdo( 'sqlite::memory:' ); $db->exec( 'create table abc (a integer, b integer, c integer);' ); $db->beginTransaction(); while( $entries-- ) { $a = mt_rand( 1, 99 ); $b = mt_rand( 100, 999 ); $c = mt_rand( 10000, 99999 ); $db->exec( "insert into abc values ($a,$b,$c);" ); } $db->commit(); $start = microtime( true ); $results = $db->query( "select a from abc where b=$b and c=$c;" )->fetchAll( PDO::FETCH_ASSOC ); echo count( $results ) . ' match, ' . round( microtime(true) - $start, 4 ) . 's to complete without index' . PHP_EOL; // build an index $db->exec( 'create index i1 on abc (c);' ); $start = microtime( true ); $results = $db->query( "select a from abc where b=$b and c=$c;" )->fetchAll( PDO::FETCH_ASSOC ); echo count( $results ) . ' match, ' . round( microtime(true) - $start, 4 ) . 's to complete with index' . PHP_EOL; $db = null; ?> mombook:~ hackermom$ php index_test.php 1 match, 0.1118s to complete without index 1 match, 0.0001s to complete with index
- doty 17y agoTry 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.