sqlite-users-request, Hello
dongsheng zhang, [EMAIL PROTECTED] 2008-07-14 ----- Receiving the following content ----- From: sqlite-users-request Receiver: sqlite-users Time: 2008-07-14, 00:00:03 Subject: sqlite-users Digest, Vol 7, Issue 43 Send sqlite-users mailing list submissions to sqlite-users@sqlite.org To subscribe or unsubscribe via the World Wide Web, visit http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users or, via email, send a message with subject or body 'help' to [EMAIL PROTECTED] You can reach the person managing the list at [EMAIL PROTECTED] When replying, please edit your Subject line so it is more specific than "Re: Contents of sqlite-users digest..." Today's Topics: 1. Re: COUNT() on indexed tables / primary key with 100'000records (Igor Tandetnik) 2. Efficiency question about LIMIT (Csaba) 3. Re: Efficiency question about LIMIT (Igor Tandetnik) ---------------------------------------------------------------------- Message: 1 Date: Sun, 13 Jul 2008 11:16:11 -0400 From: "Igor Tandetnik" <[EMAIL PROTECTED]> Subject: Re: [sqlite] COUNT() on indexed tables / primary key with 100'000records To: sqlite-users@sqlite.org Message-ID: <[EMAIL PROTECTED]> <[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 ------------------------------ Message: 2 Date: Sun, 13 Jul 2008 17:28:27 +0200 From: Csaba <[EMAIL PROTECTED]> Subject: [sqlite] Efficiency question about LIMIT To: sqlite-users@sqlite.org Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1 If I have a table and I run a query against it with a LIMIT 1000 clause, then presumably once 1000 rows matching the query have been found, SQLite can return. Does this change if I have an ORDER BY clause that does not match any index? If it does match an index, will SQLite be clever enough to use that index? The intention of the question is that I will be receiving queries from users, but if the query returns too many records, then I'd rather abort it as soon as possible. So the real intent of the question is about how SQLite aborts with respect to the LIMIT and ORDER BY clause Thanks, Csaba Gabor from Vienna ------------------------------ Message: 3 Date: Sun, 13 Jul 2008 11:37:59 -0400 From: "Igor Tandetnik" <[EMAIL PROTECTED]> Subject: Re: [sqlite] Efficiency question about LIMIT To: sqlite-users@sqlite.org Message-ID: <[EMAIL PROTECTED]> "Csaba" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > If I have a table and I run a query against it with a LIMIT 1000 > clause, then presumably once 1000 rows matching the > query have been found, SQLite can return. I'm not sure what you mean by "can return". You get one row for every call to sqlite3_step. With the LIMIT clause, you'll just get SQLITE_DONE code on 1000'th call to sqlite3_step. Or, you can simply call sqlite3_reset or sqlite3_finalize after retrieving 1000 rows, and not bother with LIMIT clause. There's no difference. > Does this change if I have an ORDER BY clause that does > not match any index? No (though it would take longer to get the first row, as SQLite will have to retrieve and sort all the records first). > If it does match an index, will SQLite > be clever enough to use that index? Yes. > The intention of the question is that I will be receiving queries > from users, but if the query returns too many records, then > I'd rather abort it as soon as possible. So the real intent of > the question is about how SQLite aborts with respect to > the LIMIT and ORDER BY clause Why not use sqlite3_progress_handler and limit by running time, rather than the number of rows? It's easy to construct a SQL statement that takes a very long time to return zero rows. Igor Tandetnik ------------------------------ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users End of sqlite-users Digest, Vol 7, Issue 43 ******************************************* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
