I would agree with the suspicion that your data is "changing shape" i.e. the cardinality of index fields is becoming very different from what ANALYZE stored.
As for bypassing the query planner/code generator you might want to contact Prakash Premkumar <prakash.p...@gmail.com> who is apparently quite determined to go this route. I think judicious use of the available hints (i.e. CROSS JOIN and USING) will get you further quicker. We almost exclusively use virtual tables here (for which ANALYZE is useless) and find CROSS JOIN to be our best friend in fixing queries that xBestIndex return values cannot coax into performing well. -----Ursprüngliche Nachricht----- Von: RP McMurphy [mailto:rpm0...@yahoo.com] Gesendet: Freitag, 14. November 2014 15:32 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR I am resending this message below from 3 days ago because it never made it to the list. RP PS: Messages seem to take a long time to go through the gmane system, at least half a day and sometimes more in my experience so far. -------------------------------------------- On Tue, 11/11/14, RP McMurphy <rpm0...@yahoo.com> wrote: Subject: Re: Discrepancy with indexing and WHERE clause with AND/OR To: sqlite-users@sqlite.org Date: Tuesday, November 11, 2014, 6:31 PM > If you can provide any examples where ANALYZE makes a query slower, I suspect the developer team would like > to see them. > After we run analyze and then let the process run for a while the DB contents change - and it can change quite considerably depending upon what is happening. I suspect that the analyze data gets stale, but I don't know how to track such things in sqlite. Anyhow we can't keep running analyze every few minutes because it takes a long time to run with our DB and it appears to block all other actions until it is done. A this point we are considering writing VDBE code directly and bypassing the parser. Has anyone else done this? Is it going to be a huge ugly can-of-worms if we do that? RP _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
