SQLite learning Manual (index and data analysis/cleanup)

I. Create an index:

In SQLite, the SQL syntax for index creation is basically the same as that of most other relational databases, because here is just an example usage:
SQLite> Create Table testtable (first_col integer, second_col integer );
-- Create the simplest index, which is based on a field in a table.
SQLite>Create IndexTesttable_idxOnTesttable (first_col );
-- Create a joint index. The index is based on multiple fields in a table and can specify the sorting rules (ascending or descending) for each field ).
SQLite>Create IndexTesttable_idx2On Testtable (first_colASC, Second_colDesc);
-- Create a unique index. This index rule is the same as that of the data table. That is, null is different from any value, including null.
SQLite>Create unique indexTesttable_idx3On Testtable (second_colDesc);
SQLite>. IndicesTesttable
Testtable_idx
Testtable_idx2
Testtable_idx3
From the output of the. Indices command, we can see that all three indexes have been created successfully.

Ii. delete an index:

The deletion of indexes is very similar to the deletion of views. Therefore, the following is an example:
SQLite>Drop IndexTesttable_idx;
-- If you delete an index that does not exist, the Operation will fail. If you do not want the error to be thrown, you can use the "if exists" clause..
SQLite>Drop IndexTesttable_idx;
Error: no such index: testtable_idx
SQLite>Drop index if existsTesttable_idx;

Iii. Re-indexing:

Re-indexing is used to delete an existing index and re-indexing the index based on its original rules. Note that if the database name is not provided after the reindex statement, all the indexes in all the attached databases connected to the current database will be rebuilt. If the Database Name and table name are specified, all indexes in the table will be rebuilt. If only the index name is specified, the specified index of the current database will be rebuilt.
-- Indexes in all databases connected to attached are rebuilt.
SQLite>Reindex;
-- Rebuild all indexes of the testtable table in the current primary database.
SQLite>ReindexTesttable;
-- Rebuild the index named testtable_idx2 in the current primary database.
SQLite>ReindexTesttable_idx2;

4. data analysis:

similar to PostgreSQL, the analyze command in SQLite is also used to analyze data in data tables and indexes and store the statistical results in the internal system table of SQLite, in this way, the query optimizer can select the optimal query execution path based on the analyzed statistical data to improve the overall query efficiency. See the following example:
-- if no parameter is specified after the analyze command, analyze the tables and indexes of all attached databases in the current connection.
SQLite> analyze ;
-- If you specify a database as the analyze parameter, all tables and indexes in the database are analyzed and statistical data is generated.
SQLite> analyze main;
-- if a table or index in the database is specified as an analyze parameter, the table and all its associated indexes will be analyzed.
SQLite> analyze main. testtable;
SQLite> analyze main. testtable_idx2;

5. data cleanup:

Compared with the vacuum commands in PostgreSQL, their functions and implementation methods are very similar. The difference is that PostgreSQL provides a finer granularity, while SQLite can only apply this command to the database, it cannot be accurate to the specified data table or index in the database. However, this is exactly what PostgreSQL can do.
When one or more data tables in a database have a large number of insert, update, and delete operations, a large amount of disk space will be occupied by deleted data, SQLite did not return the vacuum commands to the operating system until they were executed. Because the data storage in such data tables is very scattered, it is impossible to obtain better batch Io reading results during query, thus affecting the query efficiency.
In SQLite, only the master database in the current connection can be cleared, but other attached databases cannot be cleared. The vacuum command adopts the same policy as PostgreSQL when cleaning up data, that is, to create a new database file of the same size as the current database file, then, import the data in the database file to the new file. The deleted data blocks are not imported. After the import is completed, shrink the size of the new database file to an appropriate size. This command is very simple to execute, such:
SQLite>Vacuum;