> If there is no index on response(questionId, participantId), the database needs to do a full table scan
With only an index on one of the columns, Oracle at least will still use that index.
If the selectivity yields an estimated result more than -- in most cases -- about 2% of the data, the database system will not use the index.
There are MANY factors in play. Stats, parameters, histograms... it really depends.
Only if the index is covering.
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.
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...
Fairly true across different RDBMS products.
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.
<?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 indexA 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.
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.
Not necessarily. Oracle, and every other decent database, will look at stats to guess how many hits will return, comparing that with the total row count of the target table, and will often choose not to use indexes.
Say you only have an index on QuestionId, and you have 10 questions with 100 answers each. If you did a select for QuestionId = 5 and ParticipantId = 100, and included in the results or the where clause a column not in the index (the index isn't covering, and participantid and text aren't in the index), the query engine will estimate that the index will return 100 rows give or take (often stats yield wrong results), and to perform and then dereference from 100 index lookups loses way more than it gains, so it will simply do a table scan.
The modern Oracle uses a Cost-Based approach wherein the query optimizer will attempt to perform the lowest-cost opration to satisfy the query. If that means performing a full-table scan as opposed to utilizing an index, then this is what will be done. Statistics, data histograms, parameters (such as OPTIMIZER_ INDEX_COST_ADJ) all come into play.