As Sqlite does not support foreign keys with all features I just declared the fields as Integer. Now I noticed that the speed of selects are much faster with fields that are indexed, I wonder whether this was a good idea. (Silly university education, I´ve learned much about sql but nothing about database systems and there behavior :-( )
Simple Example: Two Tables ErrorDefinition and ErrorGroupDefinition, every error is in a group. Running a big Select (also other tables are involved): ... LEFT JOIN ErrorGroupDefinition AS EG1 ON ED.ParentID = EG1.ID ... (ED.ParentID is a normal Integer, not indexed) When I run the statement with explain query plan I get: 2|2|TABLE ErrorGroupDefinition AS EG1 USING PRIMARY KEY I´ve read that only one index is used per where (I think join is nothing else here) The questions: Does it mean only one index can be used per join or one index for each table of the join? Does the output mean, that the query runs over the fast index and there is nothing to improve (as long as I use the database always in that way) or does it just explain that one index is used? Or in other words, does this print mean that I use the database in perfect way (speaking only of indexes): 0|0|TABLE ActiveError AS AE USING PRIMARY KEY ORDER BY 1|1|TABLE ErrorDefinition AS ED USING PRIMARY KEY 2|2|TABLE ErrorGroupDefinition AS EG1 USING PRIMARY KEY 3|3|TABLE ErrorGroupDefinition AS EG2 USING PRIMARY KEY Best regards / Mit freundlichen Grüssen Stefan Breitholz ----------------------------------------------------------------------------------------------------------- Staeubli GmbH - Theodor-Schmidt-Str. 19 DE - 95448 Bayreuth Phone: +49(0)921/883-126 Fax: +49(0)921/883-58126 mailto:s.breith...@staubli.com http://www.staubli.com Registered under HRB 175 - Local Court Bayreuth - Managing Director: Karl Kirschner ----------------------------------------------------------------------------------------------------------- This e-mail and any attachment (the 'message') are confidential and privileged and intended solely for the person or the entity to which it is adressed. If you have received it in error, please advise the sender by return e-mail and delete it immediately. Any use not in accordance with its purpose, any dissemination or reproduction, either whole or partial, by entities other than the intended recipient is strictly prohibited. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
