"Cannot use index" with DBD::SQLite

[LonelyPilgrim]

Hail, great masters. I have returned after a long sojourn, in which I have traveled far in the paths of Perl, but am still very much a novice.

I am especially a newb to database work, and am trying to catch myself up. I feel like I am probably making a very simple and laughable mistake, so I only come to you after hours spent beating my head against this in futility, much google-searching, and throwing up my hands.

I am working with SQLite3 and DBD::SQLite (on WinXP, with ActivePerl 5.14.2), and have a fairly simple situation in which I have a table "profile," with integer primary key "profile_id", and another column "name". I want to do lookups on this table by "name", so I created an index "idx_name":

CREATE INDEX idx_name ON profile (name COLLATE NOCASE DESC);

Queries on this index work just fine when testing them on the SQLite command line:

SELECT profile_id ON profile INDEXED BY idx_name WHERE name == 'William';

However, when I try to run this in Perl, I get an error:

use strict;
use warnings 'all';
use DBI;

my $dbh = DBI->connect("dbi:SQLite:test.db","","");

my $sql = q{ SELECT profile_id FROM profile INDEXED BY idx_name WHERE 
+name == ? };
my $sth = $dbh->prepare($sql);

my $name = 'William';

my $rc = $sth->execute($name);

DBD::SQLite::db prepare failed: cannot use index: idx_name

What am I doing wrong? I appreciate your patience.

[choroba]

How did you create the table and the index? Does the index really index the "name" column? This works for me:

#!/usr/bin/perl
use warnings;
use strict;

use DBI;

unlink 'test.db' or warn $!;

my $dbh = DBI->connect('dbi:SQLite:test.db', q(), q());
$dbh->do(q(create table profile (profile_id integer, name varchar)));
$dbh->do(q(create index idx_name on profile (name, profile_id)));
my $insert = $dbh->prepare(q(insert into profile values(?, ?)));
$insert->execute(@$_) for [10, 'Joseph'],
                          [11, 'William'],
                          [12, 'Elisabeth'];

my $sql    = q{ SELECT profile_id FROM profile INDEXED BY idx_name
                WHERE name == ? };
my $sth    = $dbh->prepare($sql);
my $name   = 'William';

$sth->execute($name);
my $arr = $sth->fetchall_arrayref;
print "@$_\n" for @$arr;

If I switch name and profile_id in the index creation, I get the error you mentioned, but only if no other index has been yet defined. I am not sure this is the intended behaviour.

[LonelyPilgrim]

Thanks. I ran your code, and it worked for me, and then re-created my index per your specifications, with the one tweak I made before. It still didn't work on my table. So I figured that tweak must be the problem.

For some reason I don't understand, it didn't like my COLLATE NOCASE DESC specification. When I re-created the index once again without that, it started working. Huh.

[zyxsys.com]

When you give the COLLATE NOCASE DESC option in the index it means convert all the text to lower case and store them in descending order for case insensitive search. So, to make use of that index, you have to give the same collation again in your query, and the following should work.

SELECT profile_id FROM profile INDEXED BY idx_name WHERE name == ? COL
+LATE NOCASE

If you recreated the index without this collation, then it means you cannot do case insensitive search, or have to convert things to upper or lower case every time like below.

SELECT profile_id FROM profile INDEXED BY idx_name WHERE lower(name) =
+= lower(?)

Although you have found a workaround, this may be useful for someone else, facing a similar problem.

[Anonymous Monk]

You don't need to tell SQLite which indexes do or do not exist ...

[LonelyPilgrim]

Apparently you do, since such is specified in the documentation, and since without the "INDEXED BY," the query takes a good 10-20 seconds to run, so it is clearly not using the index. (Which may be yet another indication that my index was incorrectly created.)