Re: [Firebird-devel] Firebird feature requests

[Alex K. <al_...@uk...>]

"Dmitry Yemanov" <di...@us...>:

>> 2) Indexes should be used for unstrict search conditions
>>
>>     For example index is _used_ during execution of query below:
>>     select recid from mytable where status = 0;
>>
>>     but NOT used during execution of the following query
>>     (even if valid plan was specified);
>>     select recid from mytable where status < 1;
>
> I'm pretty sure the index _is_ used. Feel free to prove me wrong with a 
> real
> example.
    First of all I need a clarify my point a bit -- Firebird (1.5.2) reports 
that index _is_ used (i.e. you're correct) but query execution time is 
unacceptable.
    Please repeat the following test case as close as possible:

1) Create test database and table:
create database '/var/lib/databases/test.db' user 'sysdba' password 
'topsecret';
connect '/var/lib/databases/test.db' user 'sysdba' password 'topsecret';
create generator myrecid;
create table mytable (
      recid bigint not null primary key,
      status smallint
);

2) Fill the table by test data
// --------- please execute the perl script below to fill the table, cut 
below
#!/usr/bin/perl
use strict;

my $dsn = 
"DBI:InterBase:dbname=/var/lib/databases/test.db;ib_dialect=3;ib_charset=unicode_fss";
my $dbuser = "sysdba";
my $dbpassword = "topsecret";

use DBI;

my $dbh = DBI->connect( $dsn, $dbuser, $dbpassword, { RaiseError => 0 } ) or 
die( "unable to connect to database '$dsn'" );
$dbh->{ AutoCommit } = 0;
$dbh->func( -access_mode => 'read_write', -isolation_level => 
'read_committed', -lock_resolution => 'wait', 'ib_set_tx_param' );

my $i = 0;
for ( $i = 0; $i < 900000; $i++ ) {
 my $x = rand( 1 ) + 1;
 $dbh->do( "insert into mytable ( recid, status ) values( gen_id( myrecid, 
1 ), ? )", undef, $x );
}

for ( $i = 0; $i < 30; $i++ ) {
 $dbh->do( "insert into mytable ( recid, status ) values( gen_id( myrecid, 
1 ), 0 )" );
}

$dbh->commit;
$dbh->disconnect;
undef( $dbh );

// --------- end of script, stop cutting above

3) Create index
create index myidx0 on mytable( status );

4) Simulate some past table activity (important! do not skip this step)
update mytable set status = 3 where status = 1;
update mytable set status = 1 where status = 2;
update mytable set status = 2 where status = 3;
commit;

5) Please execute both queries below and compare time of execution:
    a) select recid from mytable where status = 0;
    b) select recid from mytable where status < 1;

    Also you may try to measure execution time of
    select status,count(*) from mytable group by status;

SY,
Alex Kotov