On Nov 19, 2008, at 7:57 AM, Hardy, Andrew wrote: > Out of interest why must it completely READ each entire row? Is it > because '*' has been used?
The database reads a page at a time. A page is, by default, 1024 bytes. A single page might contain multiple rows, or a single large row might be spread across multiple pages. To do a count(*), only the header of each row actually needs to be seen. For smaller rows, the header and the data are all on the same page, so the entire row ends up being read. However, if a row contains large CLOBs or BLOBs and is spread across multiple pages, only the first page (the page containing the header) is read. When rows are only about 100 bytes in size, they will all fit on a single page, so the entire row ends up being read, though only the header is decoded and interpreted. > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > ] On Behalf Of D. Richard Hipp > Sent: 19 November 2008 12:05 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Terrible performance for one of our tables > > > On Nov 19, 2008, at 3:08 AM, Jens Miltner wrote: > >> Hi, >> >> we're seeing terrible performance problems when fetching data from >> one >> of our tables: >> The table contains roughly 1.2 Million rows and a plain "SELECT >> COUNT(*) FROM t" query takes 8 minutes to finish. > > That's because "SELECT count(*) FROM t" has to read and count every > row in the table - all 1.2 million rows. And this involves reading > all 1.2 million rows from disk. If each row is 100 bytes in size, > that involves reading 120MB. > > An SQLite database does *not* maintain the number of rows in a table > as separate metadata. Many client/server database engines do > maintain such metadata. When I was designing the SQLite file > format, I deliberately choose to omit such metadata because > including it would slow down inserts and deletes. > > If you frequently need to know how many rows are in a certain table, > use insert and delete triggers to maintain the count yourself in a > separate table. Then just read out the count from the separate > table when you need it, rather than recomputing it by reading all > 1.2 million rows of the original table. > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > _______________________________________________ > 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 D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
