On Sat, Sep 11, 2010 at 12:36 AM, Max Vlasov <max.vla...@gmail.com> wrote: > Hi, > > sometimes it is necessary to return only part of columns (maybe even only > id/rowid) and sure SELECT Col1, Col2 works. But as the real tests some time > ago showed, sqlite actually reads all the data from db (sure with an > exception if record is bigger than a page). One of the approaches to > optimize this is to use index, created only on the fields needed. For one of > my application containing 70,000 records with about 200 bytes each it saved > much time when i needed to load only id on the start > > But recently I thought that the same result can be achieved with a separated > table and a trigger that keeps necessary data in sync. So, I wanted to > compare these solutions. My first pro for each. > - With the Index I didn't had to change much in the code, just create the > index and append ORDER BY (the trigger case will require special connection > in the logic of the code/classes between the tables). > - On the other hand the trigger approach looks more logical and > self-describing (you will always understand for what was it for just by > looking at the statement) in contrary to the index that looks more like a > trick or a hack. And moreover this scheme will theoretically give a similar > boost for other db engines. > > What other observations can be made about these two types of solutions that > I may face in the future?
Both are commonly used in the wild. Indexes have the added bonus over triggers that you'll have less of your own code to test and maintain, so I usually end up using them if I can. I reserve triggers for more complex operations. SQL Server even has another option -- an indexed view. -- Cory Nelson http://int64.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
