3 ms·

>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 cha

by 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 ago
Yes, 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

by ergo98 17y ago

kevingadd 17y ago

Yes, 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