On Mon, Mar 7, 2011 at 6:07 AM, J Trahair <j.trah...@foreversoftware.co.uk>wrote:
> > Questions: > 1. Do the differing orders of fields in the 2 SELECTs require me to use 2 > indexes as above? > You'll need two indices if you want your two SELECTs to run efficiently. And the two indices you propose seem correcct. But it has nothing to do with the order of the fields in the WHERE clause. > 2. In the event that other SELECTs for other tables turn out to be more > complex than these two (which they are), would a maximum of (say) 4 indexes > per table be too many? > Don't stress over indices so much. Code your application and run it. Look for performance bottlenecks and then add indices as necessary to fix them. "Premature optimization is the root of all evil". The beauty of SQL (not just SQLite but any SQL database engine) is that you can focus on the semantics of your query and not worry about the implementation - the SQL database engine will figure out the best query algorithm for you. If you add an index or two, then the engine will likely choose a different (hopefully better) algorithm. None of that requires any coding on your part. Had you been using a key/value data store, changing the algorithm would have required you to rewrite a bunch of code - which is time consuming and error-prone. SQLite has interfaces (ex: http://www.sqlite.org/c3ref/stmt_status.html) that allow you to instrument your program and let it automatically tell you about cases that might benefit from an added index. I use these interfaces, for example, in the implementation of Fossil (the DVCS that hosts SQLite and which is also uses SQLite as its on-disk storage format.) When I compile fossil with -DFOSSIL_DEBUG, and run it, any query that is not using an index will cause a warning message to be rendered on the webpages in red text. This is not something you want in a production build, but it is useful in looking for potential scalability problems. You can probably devise a similar system for whatever application you are coding. > > Thanks in advance. > > Jonathan Trahair > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
