On Sun, Sep 21, 2008 at 11:32:18PM +0200, Zbigniew Baniewski scratched on the wall: > I've created a test database with almost one million records (about 20 fields > each). I noticed, that the simple query like "select count(*) from table" > takes about 10 seconds (the database file is of about 300 MB size). > > I'm wondering: is it the limit - or is it still possible to reduce the > response time? I mean the simplest queries here, mostly: "select * from", > "...where something=''", "...where something like '%that%'. Yes, I know: > indexing.
Indexing will help with "something=" (in some cases) but not with "like '%that%'". Indexes aren't much use for something with a wildcard prefix. If you're doing a large amount of text searching and manipulating, you might look at the FTS modules to see if they fit your needs. Additionally, if you're on a desktop system with enough resources, you'll see a significant improvement by upping the cache size. In fact, if you can afford it, you can simply make the page cache large enough to hold the entire database. For standard 1K pages SQLite requires about 1.5K of RAM. So you'd need about 450MB to pull something the size of your test DB into RAM. That's not out of the question on most modern desktops, but usually isn't too practical for something like a phone or PDA. > Unfortunately, indexing won't have any effect on "count(*)". But > of course, indexing tips are welcome too (for SELECT ...). count(*) is an odd one... In most database systems it is extremely fast, but in SQLite it tends to be rather slow. Unlike most database systems, all SQLite built-in functions are implemented using the public API. As such, they don't have access to any specialized internal information about the SQLite data structures. The end result of all this is that count(*) actually does a full table scan, counting each individual row. Personally, I like the "honesty" of a system using the public API for internal works. It somehow justifies the completeness of the API, as it forces the developers to, as they say, "eat their own dogfood." Of course, I rarely find myself using something like count(*) in production code, so in this specific case I have the luxury of appreciating the motivations of the design and can disregard the practical performance issues. I realize not everyone is in that situation. If you search the archives, you'll find many discussions on the best way to create a system table that keeps track of the number of rows in each table via triggers. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
