SQLite Forum

jeebus! that was it! I ran `ANALYZE` on both `treatments` and `materialsCitations` tables. The `QUERY PLAN` changed ever so slightly (on the order of searching `treatments` and `materialsCitations` tables was flipped)

```sql
QUERY PLAN
|--SCAN TABLE vtreatments VIRTUAL TABLE INDEX 0:m
|--SEARCH TABLE treatments USING INDEX sqlite_autoindex_treatments_1 (treatmentId=?)
|--SEARCH TABLE materialsCitations USING INDEX ix_materialsCitations_treatmentId (deleted=? AND treatmentId=?)
`--USE TEMP B-TREE FOR GROUP BY
```

But the query was like night and day

```sql
sqlite> SELECT
   ...>     collectionCode,
   ...>     Count(collectionCode) AS c
   ...> FROM
   ...>     materialsCitations
   ...>     JOIN treatments ON materialsCitations.treatmentId = treatments.treatmentId
   ...>     JOIN vtreatments ON treatments.treatmentId = vtreatments.treatmentId
   ...> WHERE
   ...>     collectionCode != ''
   ...>     AND materialsCitations.deleted = 0
   ...>     AND treatments.deleted = 0
   ...>     AND vtreatments MATCH "carabus"
   ...> GROUP
   ...>     BY collectionCode;
collectionCode  c
--------------  ----------
CFDD            1
CFDD, CTAY      3
CFDD, CTAY, CZ  23
CFDD, CTAY, CZ  2
IZAS, MPU, BMN  1
NMNHS           14
NMP             6
ZISP            2
ZISP, CAK, CBK  1
ZISP, CFDD, CT  1
Run Time: real 0.259 user 0.014076 sys 0.064197
sqlite>
```

boom! thanks Dan! you saved me hours of headache trying to rewrite my query making routines.

A question – if `ANALYZE makes such a difference, why doesn't SQLite just do it anyway? I mean, is it *not* advised to `ANALYZE` under certain circumstances? Keep in mind, I didn't first `INDEX` the tables and *then* changed the `deleted` column (thereby possibly necessitating `ANALYZE`). The `deleted` column was set to 0 from the get go.