"Max Vlasov" <max.vla...@gmail.com> wrote in message news:7cb963ca1001130315o69235717n92393be027eef...@mail.gmail.com... > > >> 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.
Max, thanks for your follow up. I think we understand the guideline the same way. It says that one should not use INDEXED BY for tuning a query. I had to use INDEXED BY and NOT INDEXED to tune my query because the query optimiser mal-tuned it by ~70:1. After 3.6.17, the query optimiser tuned even worse - to ~1000:1. Operating on ANALYZE stats took what was the fastest result with a couple of INDEXED BY clauses down to ~70:1. The only way I could get the fastest result across different sqlite versions and with/without ANALYZE stats was to do the very opposite of what the docs say - i.e., use INDEXED clauses liberally to restrict what the query optimiser can do to screw up my query. I don't understand your last sentence as I am not a programmer and have no experience of working with sql assertions. > 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. That's odd. Using a SQLite manager with sqlite 3.6.21, both lines give the same error on one of my tables with 5 indexes. SELECT Surname FROM NameTable INDEXED BY idxSurname SELECT Surname FROM NameTable INDEXED BY idxSurname ORDER BY Surname COLLATE NOCASE And EXPLAIN QUERY PLAN indicates that the query optimiser used no index in both cases when the INDEXED BY clause was deleted. > 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. Assuming that this was indeed a multi-table query that worked, the query can only execute identically with/without the INDEXED clauses provided the query optimiser consistently used the same indexes. And, yes, it would throw up an error if the INDEXED BY index had been dropped. > 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. Isn't that what I demonstrated with the comparative results of EXPLAIN QUERY PLAN? The optimiser chose inappropriate indexes when allowed to do so. Your statement assumes that the query optimiser chose the same indexes as the ones I chose in iteratively tuning for fastest speed. > 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, I don't know that I have found a major flaw in the database engine or that I have thrown up a very exceptional database structure that its optimiser can't properly deal with. I most certainly had to do the opposite of what the online docs said about the use of INDEXED clauses. My tests are easily reproducible if you care to let me send you the database and queries. The SQLite manager developer who led me to investigate the change in performance around 3.6.18 of my early query that lacked INDEXED clauses has also offered the database in the thread he started titled "Performance regression....". I don't know how I can make things more simple - I'm not a programmer and my interest in sqlite is pretty narrow. I am hoping that bringing my problem and observations to this forum will elicit the necessary interest from the sqlite developer community as to why it was necessary for me to violate their very clear instruction that one should not use INDEXED clauses to tune a query. That instruction can only be correct if, and only if, the query optimiser can be relied on to tune the query optimally. In this case, it clearly does not. Thanks to the feedback so far, I have learned how to use EXPLAIN and that I was mistaken to initially report this as a speed regression. Rather it turns out to be that query optimisation has changed after 3.6.17, that there is at least one database structure for which the query optimiser is extremely sub-optimal and that it is necessary, in that situation, to override the query optimiser with INDEXED BY and NOT INDEXED clauses, contrary to the documentation. Cheers! Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
