Talk about clustered indexes, nonclustered indexes and use in SQLite.

Tags: record physical default SQL full Clust Advantage limit data

Clustered index A table can have only one, and a table of non-clustered indexes may exist multiple.

Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous.

Create clustered index dcity on city (name)//Creating a clustered Index

Create unclustered index dcity on city (name)//creating Nonclustered indexes

First, the advantages:

• Greatly speed up the retrieval of data, which is the main reason for creating indexes
• Accelerates the connection between tables and tables, especially when it is useful to achieve referential integrity of data.
• When you use grouping and sort clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

Besides the disadvantages:

• It takes time to create an index, but the problem is small, and the general index is built once
• Indexes need to occupy physical space, especially clustered indexes, which requires a large amount of space
• When the data in the table is added, deleted, and modified, the index should be maintained dynamically, reducing the maintenance speed of the data, this is a relatively large problem.

Index Overwrite: The content stored by the index is the final output of the data

SQLite does not support clustered indexes, Android requires a "_id" field by default, which ensures that the data you insert will be inserted in an integer order of "_id", and that the integer type's primary key will play the same role as the clustered index. So do not create an index on the primary key that is declared as: INTEGER PRIMARY key.

Many friends who are unfamiliar with the index create an index in the table but find that it is not in effect, which is mostly about what I'm going to say next. There are some restrictions on the columns that appear in the WHERE clause, which are related to the leading column, for the index to take effect. The so-called leading column is the first column in the creation of a compound index statement or a contiguous number of columns. For example, by creating index comp_ind on table1 (x, y, z), X,XY,XYZ is the leading column, and Yz,y,z is not. These, for other databases, may have some minor differences, here is the standard SQLite. In the WHERE clause, the leading column must use the equals or in operation, and the rightmost column can use inequalities so that the index can take effect completely. Also, the columns in the WHERE clause do not need to be fully indexed, but you must ensure that there are no gaps between the indexed columns.

Talk about clustered indexes, nonclustered indexes and use in SQLite.