|
2010-08-04
| ||
| 12:13 | Backport fix [267492d3a7eff7b] for the performance regression caused by automatic indexing and reported by ticket [8011086c85c6c4040]. (check-in: 3f367fe0 user: drh tags: branch-3.7.0) | |
|
2010-07-28
| ||
| 02:53 | • Fixed ticket [8011086c]: Query planner creates unnecessary and expensive automatic index plus 2 other changes (artifact: b5dd6bce user: drh) | |
| 02:53 | Do not allow automatic indices for the RHS of IN expressions which are not correlated subqueries. Ticket [8011086c85c6c404014c9] (check-in: 267492d3 user: drh tags: trunk) | |
| 00:33 | • New ticket [8011086c] Query planner creates unnecessary and expensive automatic index. (artifact: 333f2a70 user: drh) | |
| Ticket Hash: | 8011086c85c6c404014c947fcf3eb9f42b184a0d | |||
| Title: | Query planner creates unnecessary and expensive automatic index | |||
| Status: | Fixed | Type: | Code_Defect | |
| Severity: | Important | Priority: | Immediate | |
| Subsystem: | Code_Generator | Resolution: | Fixed | |
| Last Modified: | 2010-07-28 02:53:54 | |||
| Version Found In: | 3.7.0 | |||
| Description: | ||||
Consider the following SQL:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(x INTEGER PRIMARY KEY, y); SELECT b FROM t1 WHERE t1.a IN (SELECT x FROM t2 WHERE y=?); The query planner is creating an automatic index for the T2.Y column even though a simple linear scan of T2 would be a less costly plan. This problem was introduced in version 3.7.0, which is the first version of SQLite to support automatic indices. The work-around is to use PRAGMA automatic_index=OFF; to disable the automatic index mechanism, or to compile SQLite using the -DSQLITE_OMIT_AUTOMATIC_INDEX compile-time option. | ||||