Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

"Cannot use index" with DBD::SQLite

by LonelyPilgrim (Beadle)
on Jan 29, 2013 at 20:24 UTC ( #1015927=perlquestion: print w/replies, xml ) Need Help??
LonelyPilgrim has asked for the wisdom of the Perl Monks concerning the following question:

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.

Replies are listed 'Best First'.
Re: "Can't use index" with DBD::SQLite
by choroba (Bishop) on Jan 29, 2013 at 21:12 UTC
    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.
    لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

      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.

        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.

Re: "Can't use index" with DBD::SQLite
by Anonymous Monk on Jan 29, 2013 at 21:42 UTC
    You don't need to tell SQLite which indexes do or do not exist ...
      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.)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1015927]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2018-01-19 10:13 GMT
Find Nodes?
    Voting Booth?
    How did you see in the new year?

    Results (217 votes). Check out past polls.