> > After much playing about, I have determined that it is necessary to violate > the guidelines on not using INDEXED BY and NOT INDEXED in an attempt to > optimise performance.
Although you're very determined about your conclusions, I saw a misunderstanding about INDEXED BY in your statements. The docs say "The INDEXED BY clause is *not* intended for use in tuning the preformance of a query". From my point of you one should think about these extensions as sqlite's sql equivalent of assertions from high-level languages. For example, a quick test. I have a table Contacts with an index ids_Contacts using field CenterId. If I try to execute SELECT * FROM Contacts INDEXED BY idx_Contacts sqlite says: "cannot use index: idx_Contacts". Only when a change made adding explicit ORDER BY clause applied SELECT * FROM Contacts INDEXED BY idx_Contacts ORDER BY CenterId ... the query executes successfully. If I remove now INDEXED BY from the latter statement nothing will change in how the query is executed, _only_ if someone excplicitely deletes the index from the database. So any of your statements that argues that adding or removing INDEXED BY affects the way your queries is executed is have to be double checked. If you're still sure that you found a major flaw in the most deployed sql database in the world, please narrow your tests and conclusions to something easier reproducible. Everyone understands that you're solving one particular problem related to you, but if you find time to make things more simple, it will be to everyone's benefit. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
