1. Create an index:
In SQLite, the SQL syntax for creating indexes is basically the same as most other relational databases, because here is just a sample usage:
sqlite> CREATE TABLE testtable (first_col integer,second_col integer);
--Create the simplest index based on a field in a certain table.
sqlite> CREATE INDEX testtable_idx ON testtable(first_col);
--Create a joint index, which is based on multiple fields of a certain table, and can also specify the sorting rules for each field (ascending/descending order).
sqlite> CREATE INDEX testtable_idx2 ON testtable(first_col ASC,second_col DESC);
--Create a unique index, the index rule is the same as the uniqueness constraints of the data table, that is, NULL and any value are different, including NULL itself.
sqlite> CREATE UNIQUE INDEX testtable_idx3 ON testtable(second_col DESC);
sqlite> .indices testtable
testtable_idx
testtable_idx2
testtable_idx3
From the output of the .indices command, it can be seen that all three indexes have been successfully created.
2. Delete the index:
The deletion of indexes is very similar to the deletion of views, and the same is true, so here is just an example:
sqlite> DROP INDEX testtable_idx;
--If deleting an index that does not exist will cause the operation to fail. If you do not want the error to be thrown in uncertain situations, you can use the "IF EXISTS" clause.
sqlite> DROP INDEX testtable_idx;
Error: no such index: testtable_idx
sqlite> DROP INDEX IF EXISTS testtable_idx;
3. Reconstruct the index:
Reconstructed index is used to delete an existing index and reconstruct the index based on its original rules. It should be noted here that if the database name is not given after the REINDEX statement, then all indexes in all Attached databases under the current connection will be rebuilt. If the database name and table name are specified, all indexes in the table will be reconstructed. If only the index name is specified, the specified index of the current database will be reconstructed.
--The indexes in all databases currently attached are rebuilt.
sqlite> REINDEX;
--Reconstruct all indexes of the testtable table in the current main database.
sqlite> REINDEX testtable;
--Reconstruct the index in the current main database with the name testtable_idx2.
sqlite> REINDEX testtable_idx2;
4. Data analysis:
Very similar to PostgreSQL, the ANALYZE command in SQLite is also used to analyze data in data tables and indexes, and store statistical results in SQLite's internal system tables, so that the query optimizer can select the optimal query execution path based on the analyzed statistical data, thereby improving the efficiency of the entire query. See the following example:
--If no parameters are specified after the ANALYZE command, the tables and indexes in all Attached databases in the current connection are analyzed.
sqlite> ANALYZE;
--If the database is specified as a parameter of ANALYZE, all tables and indexes under the database will be analyzed and statistics will be generated.
sqlite> ANALYZE main;
--If a table or index in the database is specified as ANALYZE parameter, then the table and all its associated indexes will be analyzed.
sqlite> ANALYZE ;
sqlite> ANALYZE main.testtable_idx2;
5. Data cleaning:
Compared with the VACUUM command in PostgreSQL, their functions and implementation methods are very similar. The difference is that PostgreSQL provides a finer granularity, and SQLite can only use this command on the database and cannot be accurate to the data table or index specified in the database. However, this is exactly what PostgreSQL can do.
When there are a large number of insert, update, and delete operations in one or more data tables in a database, a large amount of disk space will be occupied by the deleted data. SQLite does not return them to the operating system before the VACUUM command is executed. Since the data storage in this type of data table is very scattered, it is impossible to get a better batch IO reading effect when querying, which affects the query efficiency.
In SQLite, only the primary database in the current connection is supported, and other Attached databases cannot be cleaned. When completing data cleaning, the VACUUM command adopts the same strategy as PostgreSQL, that is, create a new database file of the same size as the current database file, and then import the data in the database file into the new file in an organized manner. The deleted data blocks will not be imported. After completing the import, the size of the new database file is reduced to the appropriate size. The execution of this command is very simple, such as:
sqlite> VACUUM;