On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко <grigore...@mail.ru>wrote:
> > > > Please post the results of: > > > > SELECT * FROM sqlite_stat1; > > > > > tbl = log > idx = idxlog_kind_computer_process_who_id_msg_created_at > stat = 2815667 563134 563134 469278 74097 1 1 1 > The first number on "stat" is the number of rows in the table. The 2nd number is the average number of rows that have the same value for the first column of the index. The 3rd number is the average number of rows that have the same value for the first 2 columns of the index. And so forth. There are a huge number of rows that have the same value for the first 4 terms of this index, which shows us that this is a really lousy index. Ideally, you want the second number in the "stat" column to be something small, like 10. If you do not run ANALYZE, SQLite has no way of knowing that the index is mostly useless. SQLite assumes that the index is a good one, and that the 2nd integer in "stat" is 10. And it therefore tries to use the index. But since the index is so bad, the resulting performance is slow. After running ANALYZE, SQLite realizes that the index is lousy and avoids using it. Hence, performance is much better. > > tbl = log > idx = idxlog_kind_computer_process_id_who_msg_created_at > stat = 2815667 563134 563134 469278 1 1 1 1 > > tbl = log > idx = idxlog_kind_computer_id_process_who_msg_created_at > stat = 2815667 563134 563134 1 1 1 1 1 > > tbl = log > idx = idxlog_kind_id_computer_process_who_msg_created_at > stat = 2815667 563134 1 1 1 1 1 1 > > tbl = log > idx = idxlog_kind_computer_process_who_msg_created_at > stat = 2815667 563134 563134 469278 74097 2 2 > > tbl = log > idx = idxlog_created_at > stat = 2815667 106 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
