mORMot Open Source

zhangguichao

Member

Registered: 2023-03-21

Posts: 29

Ask a query question

If an index other than the ID field is created, the count (*) speed of sqlite will increase significantly, resulting in a data overhaul of 10000000 entries. Who knows why?
Only ID index   4s
Add another index  1.3s

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,263

Website

Re: Ask a query question

It depends on the WHERE clause for your count(*) query itself.

Look at the query plan.
I guess your SELECT is using the new index for computing the count(*).

Look at https://www.sqlite.org/optoverview.html for reference.

zhangguichao

Member

Registered: 2023-03-21

Posts: 29

Re: Ask a query question

I did not use the 'where' condition, just simply obtained the total number of tables. I will try again:)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,263

Website

Re: Ask a query question

Ask for the QUERY PLAN of the query. It is logged by the ORM in verbose mode.

It seems that a "select count(*) from table" generates a full scan - perhaps only on the index in latest version, I don't know.
https://marc.info/?l=sqlite-users&m=112679232205850
https://stackoverflow.com/a/9338276/458259
This is a SQLite limitation/feature.

So either store the count elsewhere, or cache it.
Most of the time, we don't need the actual count. A SELECT MAX(ROWID) is likely to be enough for a wild guess.
Or "select RowId from table limit 1" is enough to check if the table is void. This is what IRestOrm.TableHasRows executes.
Why do you need the exact count? Why not use the ORM?

zhangguichao

Member

Registered: 2023-03-21

Posts: 29

Re: Ask a query question

Just because I need to synchronize some data to MSSQL, but there are some deletion operations , which results in unequal IDs on both sides. Therefore, I need count (*) to determine where to continue synchronizing.（Mainly because I didn't consider the issue of deletion at the beginning） 。
I have been using Mormot, which is a great framework。
Thank you very much for your answer。