Hi Igor, Foreign keys are about maintaining the integrity of your database not performance so using a foreign key and creating an index are two different subjects.
The foreign key will prevent an application from creating entries in the table that don't have a matching entry in the parent end of the foreign key specification. Remember that foreign key enforcement is off by default in SQLite and you must issue a PRAGMA foreign_keys=ON every time you open the database to enforce them. Whether to have an index on any column, not just foreign keys, is more complicated. If there are a small number of entries in the table with the foreign key or if the number of unique values of the foreign key is small compared to the total number of entries in the table, it probably isn't worth creating an index. I think I read somewhere that unless the number of unique values is 20% or more, using an index may not result in any performance. improvements Really the only way to check the performance is to use EXPLAIN QUERY PLAN with and without an index, using SELECT statements that are exactly the same as will be used by your application. As for adding the foreign key specification to an existing table, there are a number of third party products that will do that for you along with many other DDL functions that are not provided by the SQLite DDL. My SQLiteAdmin program is one of them, available at www.lcsql.com. Pete lcSQL Software <http://www.lcsql.com> > So basically what you said is: > "Don't make a foreign key. Instead create an index on the foreign key > field and it will speed > things up" > > Am I right? > > What about making both foreign key and index? Will this improve it even > further? > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
