On 15 Nov 2013, at 12:55pm, L. Wood <lwoo...@live.com> wrote: > The states are relatively few (50). There are tens of thousands of companies. > > * If I frequently do queries like this: > "SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';" > what index should I use? > Should I use (company_stock_symbol), (state,company_stock_symbol), or > (company_stock_symbol,state)?
/You/ shouldn't use any of them. You should let SQL pick what it needs. But here's how to find out the answer to questions like that yourself if you don't understand how indexing works. 1) Create your table(s) and put in convincing data. 2) Run your query and make sure it returns the answers you want/expected. 3) Create all the indexes in all the orders you think could possibly be useful. 4) Run ANALYZE. 5) Use EXPLAIN QUERY PLAN on your query and find out which indexes were used 6) DROP all the indexes that weren't used. After you've done this for 5 or 10 projects you'll find that most of the time you can make good guesses yourself and don't need to keep experimenting. > * Does the answer change if the query condition is reversed? Like so: > "SELECT * FROM Foo WHERE state='baz' AND company_stock_symbol='bar';" No. SQLite analyzes your WHERE clauses in such a way that the order you originally specified the parts doesn't matter and has (almost) no influence on what's done. You can write your SQL commands in the way that makes most sense to you, the programmer, and let the computer do the hard work. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
