On 18 Apr 2010, at 7:08pm, Mike Goins wrote:
>> Also, I don't recall your saying whether a single composite index was faster
>> than separate indexes? Is it?
>>
>> Regards
>> Tim Romano
>
> Interesting suggestion. Separate indexes look to be slightly faster
> than the composite.Separate i
> Just guessing, but column 'path' probably has greater cardinality than
> column 'extension'. What happens if you reverse the order of these columns
> in the index?
> i.e. (basename, path, extension, deleted)
(deleted, basename, extension, path)
This order actually provides the most to least sig
Just guessing, but column 'path' probably has greater cardinality than
column 'extension'. What happens if you reverse the order of these columns
in the index?
i.e. (basename, path, extension, deleted)
Also, I don't recall your saying whether a single composite index was faster
than separate index
On 16 Apr 2010, at 1:17pm, Mike Goins wrote:
> Table structure:
> CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
> INTEGER default 0 );
>
> Index:
> CREATE INDEX fullpath_idx on tb_file (basename, extension, path
On Fri, Apr 16, 2010 at 8:17 AM, Mike Goins
wrote:
> Query:
> SELECT tb_file_key, basename, extension, path FROM tb_file WHERE
> basename GLOB 'a*' AND extension GLOB 'b*' AND path GLOB 'c*' AND
> deleted = 0 ORDER BY tb_file_key DESC;
Apologies for responding to my own post (digest subscriber
Sorry, this may look a bit familiar.
Table structure:
CREATE TABLE tb_file (tb_file_key INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT , basename TEXT, extension TEXT, path TEXT, deleted
INTEGER default 0 );
Index:
CREATE INDEX fullpath_idx on tb_file (basename, extension, path, deleted);
Example in
6 matches
Mail list logo
