Summary of SQLite knowledge in iOS development

I. Query Optimization

1. Use Indexes

When there is too much data in the table, creating an index can significantly increase the query speed.

Create an index

 
 

  
  
SQL> CREATE INDEX name ON table (Field 1 [ASC, DESC], Field 2 [ASC, DESC]...)
 
 

Delete Index

 
 

  
  
SQL> DROP INDEX name
 
 

Re-Indexing

 
 

  
  
SQL> REINDEX [index name, table name]
 
 

2. Data Analysis

The ANALYZE command analyzes the index of a table to store the statistical information about the index in a special table in the database. The query optimizer can use this table to make better index selection. If no parameter is provided, all indexes in all attached databases are analyzed. If the parameter specifies the database name, all indexes in the database are analyzed. If the table name is given as a parameter, only the indexes associated with the table are analyzed.

 
 

  
  
SQL> ANALYZE [index name, table name]
 
 

Ii. data cleanup

SQLite does not release the large amount of data in the table. You need to run the following command to streamline the database:

 
 

  
  
sqlite> VACUUM; 
 
 

Iii. Data Encryption

1. compile your own source code

Implement the encryption and decryption interface reserved in the source code

2. Use SQLCipher

The source code is here

The usage of step by step is here. Note that the underscores of several Macros in the article are not added. Do not copy and paste them blindly.

After the SQLCipher source code is compiled in the previous step, there will be an sqlite command line program in the directory. You can use this program to encrypt the local database and use it conveniently in the program. View his document for specific commands

4. View tools

The viewing tool in MesaSQLiteMac OS X is slow when there is a large amount of data in the table.

SQLite Manager plug-in FireFox

V. Third-party encapsulation for iOS development

1. convenient access to FMDataBase and convenient use in multiple threads

2. sqlitepersistentobjects directly enables the NS Object to have the save and load function, allowing users to use SQLite without writing SQL statements

No updates

Refer:

1. http://www.cnblogs.com/stephen-liu74/archive/2012/01/14/2322335.html

2. http://sqlcipher.net

Edit recommendations]

1. 10 suggestions for optimizing MySQL statements
2. Comment: SQL Server 2012
3. 11 important Database Design Rules
4. Enhanced summary techniques in SQL Server 2008
5. Twitter will open source its MySQL