Hello Lets say i have 120'000 records and worst case is that upto 90'000 records match the conditions.
Daniel ----Ursprüngliche Nachricht---- Von: [EMAIL PROTECTED] Datum: 13.07.2008 17:16 An: <sqlite-users@sqlite.org> Betreff: Re: [sqlite] COUNT() on indexed tables / primary key with 100'000records <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I have following table with around 100'000 rows / ~10MB on a embedded > device: > > CREATE TABLE 'fs_main' ( > 'fs_recid' INTEGER PRIMARY KEY NOT NULL, > 'fs_contenttype' INTEGER, > 'fs_itemtype' INTEGER, > 'fs_job' INTEGER, > 'fs_textid' TEXT, <- ~5 chars per Record > 'fs_flag1' INTEGER, > 'fs_object' BLOB <- ~100 Bytes per Record > ); > > Indexed by: > > CREATE INDEX 'index_fs_itemjobcontent' ON fs_main ( > fs_itemtype ASC, > fs_job ASC, > fs_contenttype ASC, > fs_recid ASC > ); > > I need to count different result sets and i'm doing that this way: > > SELECT COUNT(fs_recid) AS num FROM fs_main WHERE ( fs_itemtype=18 ) > AND fs_contenttype=2 AND fs_job=1 > > ...which takes around 4 sec. How many of your records satisfy this condition? If the condition selects 10% or more of all records, then not using the index may actually be faster. If you want to try suppressing the index, use "WHERE +fs_itemtype=18 ..." (note the unary plus). Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
