Use indexes in Android database queries-dramatically increase database operation speed

Reference:

http://blog.csdn.net/dongyuxi1987/article/details/8239739

1. Introduction to Database Indexing
Recall the steps of the dictionary in Childhood, the concept of index and dictionary directory is consistent. The dictionary directory allows us to find the pages of content we need without flipping through the dictionary and then flipping over to that page. Indexes, too, are an expression of how records are sorted by multiple fields. Indexing a field in a table creates another data structure that holds the values of the fields, and each value also includes pointers to records related to it. In this way, it is not necessary to query the entire database, naturally improve the efficiency of the query. At the same time, the data structure of the index is sorted so that binary lookups can be performed on it, which is faster.

2. Use of indexes

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.

Create an index with the following statement: Creation index IDX_EX1 on Ex1 (a,b,c,d,e,..., y,z); Here is a query statement: ... WHERE a=5 and B in (All-in-one) and C is NULL and d= ' Hello ' This is obviously valid for ABCD four columns because only equals and in operations, and is the leading column. Look at a query statement: ... Where a=5 and B in (All-in-one) and c>12 and d= ' Hello ' there is only a, B and C indexes that are valid, and the index of the D column is invalidated because it is on the right side of column C, and the C column uses inequalities, depending on the constraints of the use inequalities , column C already belongs to the far right. Finally, look at one more article: ... WHERE b in (All-inclusive) and C not NULL and d= ' Hello ' index will not be used because the leading column is not used, this query will be a full table query. 

Create the following tables and indexes:
Db.execsql ("CREATE table if not exists T1 (A, b)");
Db.execsql ("CREATE index if not exists IA on T1 (A, b)");
Insert 100,000 data, and do the following for each table:
SELECT * from t1 where a= ' 90012 '
Insert: INSERT INTO T1 (A, B) values (' 10008 ', ' name1.6982235534984673 ')
Updated: Update t1 set b= ' name1.999999 ' where a = ' 887 '

Delete: Delete from t1 where a = ' 1010 '

The data is as follows (5 different operations averaging):
Operations are indexed without indexes
Query 170ms 5ms
Inserting 65ms 75ms
Update 240ms 52ms
Delete 234ms 78ms

You can see a significant increase in query speed, a slight slowdown in insertion speed, and a slight increase in the speed of updating data and deleting data.

If you change the column in the WHERE clause in the update and delete to B, the speed is the same as no index because the index is invalid.

Use indexes in Android database queries-dramatically increase database operation speed