Thank you, thank you, Igor! This relates to the earlier thread I had started titled "Speed regression after 3.6.17" where I was wrestling with a very slow query because the query optimiser was selecting an inappropriate index, one that was very chunky because the field was logical (0,1). This led to officially inappropriate use of the INDEXED BY clause to override the query optimiser or the creation of a temporary table from a query without that field as a constraint so it that would run quickly and the temp table could be queried quickly with the otherwise offending complaint. Your idea of masking the field from the query optimiser with a simple operator works magic. On one test of a query that links the table to itself took 1130 seconds using the bare field as the constraint, 0.291 seconds with the + operator incorporating the field into an expression, an improvement by a factor of 3883!! EXPLAIN QUERY PLAN clearly showed the switch from the inappropriate index to the right one.
Great tip! Tom "Igor Tandetnik" <itandet...@mvps.org> wrote in message news:hkh4a0$mk...@ger.gmane.org... > Kelly Jones wrote: >> I have a query that runs very quickly and returns no results: >> >> SELECT * FROM filebackup WHERE sha1='x'; >> >> However, the more restrictive query below runs very slowly, although >> it obviously can't have any results either: >> >> SELECT * FROM filebackup WHERE sha1='x' AND refid=0; >> >> I have indexes on both sha1 (string) and refid (int). Only issue I >> see: this is a large table and refid=0 for 90%+ of the rows. > > For whatever reason, SQLite decides to use an index on refid for this > query. You can suppress it like this: > > SELECT * FROM filebackup WHERE sha1='x' AND +refid=0; > > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
