SQLite Analyze Command?

Databases
1

Hi,
my app creates and uses an SQLite database.

I’m aware of the Analyze command, but not quite sure what it actually does, to improve the performance for the user?

The website has this explanation, but I still have no idea what that actually means in layman’s terms:

Am I correct in thinking that this has no real visible performance effect for the user?
I was under the impression that it somehow tidied the database - i.e. had some kind of housekeeping / cleaning effect?

Could anyone clarify (in layman’s terms) ?
Also, has anyone experienced corrupted databases / erratic behaviour after analysing them - as I have seen a few posts on various sites which say they had indexing problems after running the command?

Thanks in advance.

2

Richard you ok? You look pale :stuck_out_tongue:

The visible performance would be there if the “analyze” command results in more optimal queries. If you do not make changes to your database schema/structure then analyze will just update the stats tables with the same data they hold.

So… if you are not changing anything then it will have no effect. If you do change things it may or may not depending on the changes.

3

Thanks Peter.
Bit cold here in the UK - thanks for asking :slight_smile:

4

Think of analyze as providing the query optimizer with cached info to draw upon.

5

So it wouldn’t really make that much of a difference then, unless there were literally thousands of rows?

6

The amount of data does not matter to analyze. It is analyzing the structure of the database including indices to save “hints” for the query optimizer to draw from.

Perhaps an expert can provide a more thorough explanation. Norm?

7

Hmm, I think I will remove this feature from my app, as I still can’t understand what benefit that actually provides for my particular situation?

Thanks Peter.

8

If you have a ton of tables and use joins frequently then it can have a big impact. Simple queries on small tables will unlikely see any benefit. The more involved the query the more likely it would help.

9

My database has 1 table, probably 300 entries maximum, and I only ever use basic statements such as Select * From or Insert Into in my prepared statements.