You are right. There's a way of normalizing and it will certainly reduce database size. Let's say it'll shrink by 50%. Still, if first-time runnning query timing will change from 2 min to 1 min it is still not acceptable. I cannot wait even a minute without logging.
So, before restructuring database and re-writing code I just want to understand SQLITE behaviour. The question is: does SQLITE need to load whole index to start using it? Or SQLITE can use partial loads to quickly find records it needs (defined by columns in conditions)? > You need to normalize your database. > > > > > > > > Though it's easy to put everything in one table it's horrendously inefficient > for indexing. > > > > Your indexes are huge as you're putting strings in them. > > > > > > > > Ideally you should never have a string in an index if you can avoid it. Too > much space, too long a time to index, to long a time to compare. > > > > > > > > So for starters create a "kind" table and "computer" table and just put the > _rowid into your table instead of the string. > > > > > > > > I'll bet you things will run a lot faster and your database will shrink. > > > > > > > > Same goes for other strings. > > > > > > > > Michael D. Black > > > > Senior Scientist > > > > NG Information Systems > > > > Advanced Analytics Directorate > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
