On 26 Nov 2009, at 11:02am, Jonas Sandman wrote: > If you have a join in an SQL-query, how do you add the statements to > optimize the query in an index?
Create good indexes. SQLite uses its own cleverness to pick which of the available indexes is the best one. It is much better at picking the best index than a human is. > For example: > > select a.* from animals a > join animaltype at on at.id=a.typeid > where a.name='Monkey' and a.subspecies=2 Make an index on the 'name' and 'subspecies' columns for the table 'animals' -- one index on both columns, not two separate indices. This helps the WHERE. Make an index on the 'id' column for the table 'animaltype' (exists already if 'id' is the primary key). This helps the JOIN. By the way, you do not have to use different names for tables if you don't want to. Your SELECT command would work equally well as select * from animals join animaltype on animaltype.id=animal.typeid where animal.name='Monkey' and animal.subspecies=2 and perhaps be more readable. A harder version of your question would be something about the best index for something like select * from animals join animaltype on animaltype.id=animal.typeid where animal.name='Monkey' and animaltype.genus=2 Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
