使用PHP将MySQL查询输出为SQLite

Question

We have an iOS app which must download a large amount of user data from a remote server (in JSON format) and then insert this data into the local SQLite database. Because there is so much data, the insertion process takes more than 5 mins to complete, which is unacceptable. The process must be less than 30 seconds.

We have identified a potential solution: get the remote server to store the user's data in an SQLite database (on the remote machine). This database is compressed and then downloaded by the app. Therefore, the app will not have to conduct any data insertion, making the process much faster.

Our remote server is running PHP/MySQL.

My question:

• What is the fastest and most efficient way to create the SQLite database on the remote server?
• Is it possible to output a MySQL query directly into an SQLite table?
• Is it possible to create a temporary MySQL database and then convert it to SQLite format?
• Or do we have to take the MySQL query output and insert each record into the SQLite database?

Any suggestions would be greatly appreciated.

Answer 1

I think it's better to have a look at why the insert process is taking 5 minutes.

If you don't do it properly in SQLite, every insert statement will be executed in a separate transaction. This is known to be very slow. It's much better to do all the inserts in one single SQLite transaction. That should make the insert process really fast, even if you are talking about a lot of records.

In pseudo code, you will need to the following:

SQLite.exec('begin transaction');
for (item in dataToInsert) {
    SQLite.exec('insert into table values ( ... )');
}
SQLite.exec('end transaction');

The same applies by the way if you want to create the SQLite database from PHP.

You can read a lot about this here: Improve INSERT-per-second performance of SQLite?