Multi tool use

How does sqlite select the index when querying records?

Background 

I am an iOS developer, and we use CoreData which uses sqlite database to store data in the disk in our project. Several days before one of our users said that his interface is not fluent in some case when using our app which version is 2.9.9. After some efforts we finally found that it is due to the bad efficiency when querying records from sqlite. But after updating to the latest version 3.0.6, the issue disappeared. 

Analyze

(1) when querying records from sqlite, the SQL query is

'SELECT * FROM ZAPIOBJECT WHERE ZAPIOBJECTID = "xxx" AND Z_ENT == 34'

In the version 2.9.9 of our app, the schema of table ‘ZAPIOBJECT’ of the sqlite shows

'CREATE INDEX ZAPIOBJECT_Z_ENT_INDEX ON ZAPIOBJECT (Z_ENT);'

'CREATE INDEX ZAPIOBJECT_ZAPIOBJECTID_INDEX ON ZAPIOBJECT (ZAPIOBJECTID);'

and the query plan shows

'0 0 0 SEARCH TABLE ZAPIOBJECT AS t0 USING INDEX ZAPIOBJECT_Z_ENT_INDEX (Z_ENT=?)’

which uses the less efficient index ‘Z_ENT’ (cost ~4s for 1 row).

(2) In the version 3.0.6 of our app, the SQL query is the same:

'SELECT * FROM ZAPIOBJECT WHERE ZAPIOBJECTID = "xxx" AND Z_ENT == 34'

but the schema of table ‘ZAPIOBJECT’ of the sqlite shows:

‘CREATE INDEX ZAPIOBJECT_Z_ENT_INDEX ON ZAPIOBJECT (Z_ENT);’

‘CREATE INDEX Z_APIObject_apiObjectID ON ZAPIOBJECT (ZAPIOBJECTID COLLATE BINARY ASC);’

and the query plan shows

‘0 0 0 SEARCH TABLE ZAPIOBJECT AS t0 USING INDEX Z_APIObject_apiObjectID (ZAPIOBJECTID=?)’

which uses the more efficient index ‘ZAPIOBJECTID’ (cost ~0.03s for 1 row).

(3) the total number of records in the table 'ZAPIOBJECT' is about 130000, and the index ‘ZAPIOBJECTID’ which distinct count is more than 90000 is created by us, while the index ‘Z_ENT’ which distinct count is only 20 is created by CoreData.

(4) the versions of the sqlites in the two versions of our app are the same 3.8.8.3.

Questions

(1) how sqlite select index when querying records? In the document Query Planning I learn that sqlite would select the best algorithms by itself, however in our case selecting the different index can lead to obvious efficiency.Does the difference between the creation of ‘ZAPIOBJECTID’ in two version of our app lead to the different index adopted by sqlite?

(2) It seems that those users whose system version is lower than iOS 11 would have this issue, so how can we solve this problem for them? Can we set ‘ZAPIOBJECTID’ as the designated index with CoreData API?

1 Answer
1

SQLite uses the index that results in the lowest number of estimated I/O operations.
The details of that estimation change in every version.

See the Checklist For Avoiding Or Fixing Query Planner Problems.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.