On 21/nov/06, at 17:07, Christian Smith wrote:
Sergio 'OKreZ' Agosti uttered:
Hi, I've a performance problem with an application written in
realbasic, using a sqlite db: some of the tables have UNIQUE
fields, and I've no problem with the default COLLATE mode
(BINARY); but if I set COLLATE NOCASE on the UNIQUE fields my
queries are a lot slower (in particular a SELECT query takes about
10 times to run...).
Have someone ever encountered similar problems?
UNIQUE columns use an index to keep track of unique values. Chances
are, with default COLLATE BINARY, the unique index is being used by
the SELECT QUERY to fetch records. With the COLLATE NOCASE, the
index may not be being used, with a full table scan being used
instead. To check, use:
EXPLAIN QUERY PLAN <select stmt>
This will list which tables are being scanned, and whether indexes
are being used.
Thanks. Actually the unique indexes weren't being used because I
didn't specify COLLATE NOCASE in the TABLE creation, but only in the
INDEX creation. With both COLLATE NOCASE specified it works.
-----------
Sergio 'OKreZ' Agosti
-----------
icq: 112421063
msn: [EMAIL PROTECTED]
skype: sergio.agosti
iChat: sergio.agosti
jabber: sergio.agosti
-----------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
