> s.breith...@staubli.com wrote: > > Now I noticed that the speed of selects are much faster with fields > > that are indexed > > Well, that's pretty much the point of having an index in the first > place. Why do you find this fact surprising?
Because the indexed field that improved my program was a unix timestamp already in asc order (There was another topic in the mailing list about where clause on timestamps searching t > a And t < b ) > > > I´ve read that only one index is used per where (I think join is > > nothing > > else here) > > No - one index per table. SQLite could use as many indexes as tables > mentioned in the FROM clause. > > > The questions: > > Does it mean only one index can be used per join or one index for each > > table of the join? > > One for each table. Ahh Ok! So in the print I can see which index is used for each table in the whole query. That clears nearly all the questions I posted. Thanks > > > 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? > > I don't understand this question. Nearyl explained in your answer above. So when I have a query over serveral tables normally the primay key is set as index when the table is joined. If I need to add a where clause to a field of the joined table no single index on that field can be used. So have to think about the best multiple index including the ID... > > > 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 > > Well, it's hard to prove that there's absolutely no better way to > execute a query - especially since you don't actually show the query in > question, nor the definition of tables involved. > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users 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
