On Mon, Sep 22, 2008 at 01:13:30AM +0200, Zbigniew Baniewski scratched on the wall: > On Sun, Sep 21, 2008 at 05:22:46PM -0500, Jay A. Kreibich wrote: > > > count(*) is an odd one... In most database systems it is extremely > > fast, but in SQLite it tends to be rather slow. > > [..] > > 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. > > The problem is, that it's not only about global number of records
Oh, sure... I wasn't very clear about that. What I said about count(*) being slower in SQLite only applies to global table counts, such as "SELECT count(*) FROM <table>;" where there is no WHERE clause. Most database systems can optimize this type of query just use the internal table structures to return an answer almost instantly. As soon as you add a WHERE clause, then database systems are forced to do some kind of lookup via full table scan or index searches, however. There is no significant performance different in these types of situations. That said, assuming the WHERE clause can use an available index, indexes can be used to speed up a conditional search, including testing for the presence of a row (as described in your other email). > - I would > to have a possibility to quickly count number of records found by > conditional queries like: "select count(*) from table where <condition>". From a performance standpoint, the "count(*)" part of this statement is mostly irrelevant. If you can speed up the WHERE condition, you should speed up any type of count(). > Partially it can be solved by moving the task to the application (fetch, > then count list size), although not always I want to fetch all that data. That's not going to be any faster, although the difference in SQLite vs a traditional network-based client/server system is going to be much less. Regardless, returning values is still going to take more resources and more time than just counting them. > It wasn't a problem, when I was counting several thousands of records - but, > as I can see, it will be inconvenient in the case of the larger table. It sounds like you need to take a more general approach to speeding up your queries. If you've not yet looked at building appropriate indexes, that seems like a good place to start. -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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
