SQLCipher 4.7.2 Commercial edition is way faster. Why?

[jcrabanal]

In the previous issue (thanks for solving it) I've sent you a large database of 100 MB. This database had a huge issue with certain query, it took a whole minute to return data.

I've tested this database with standard unencrypted android SQLDatabase API and SQLCipher 4.6.0 community edition, and the results were the same in both cases, 60 secs aprox to return the data.

I've analyzed with EXPLAIN QUERY PLAN the SQL query to look for temporal indexes that were created for each query, and found that 9 indexes were missing. So I've created them and ran both tests and the queries took 1.5 secs to complete.

However, when I ran the same test with SQLCipher 4.7.2 Commercial edition, I was surprised to see that the unindexed database took only 2-3 seconds aproximately, and the indexed database took 1 second.

This is all great, but the question is why? Why is a poorly indexed database that fast on the commercial edition?

[developernotes]

Hi @jcrabanal,

Thank you for reaching out. While we expect the Commercial edition of SQLCipher to be faster in certain scenarios compared to the Community edition, I don't suspect this case is related based on the sample database you provided. Your provided database was unencrypted, so the entire SQLCipher subsystem is not in play when performing queries. Based on your report you tested the following:

• android.database.sqlite
• SQLCipher for Android 4.6.0 Community edition
• SQLCipher for Android 4.7.2 Commercial edition

If you were testing against a non-encrypted SQLite database file, the underlying SQLite version bundled within the library would be different, which may also produce different query planner results. You would need to x-reference the Android API version to determine the SQLite version you were testing with when using android.database.sqlite. For SQLCipher for Android 4.6.0 Community edition, the upstream SQLite version was 3.45.3, and for SQLCipher for Android 4.7.2, the upstream SQLite version was 3.49.1.

[jcrabanal]

No, I have code to encrypt any plaintext database the app tries to open.

public static void encryptDatabase(@NonNull File fDb, @NonNull String sDbPassword) {
    if (TextUtils.isEmpty(sDbPassword)
            || !fDb.isFile()
            || !isPlaintextDatabase(fDb)) {
        return;
    }
    File fDbEncrypted = new File(fDb.getAbsolutePath() + ".temp_sec");
    CryptoUtils.deleteFile(fDbEncrypted);
    SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(fDb,
            CryptoUtils.EMPTY_STRING,
            null,
            (dbCorrupted, ex) -> CryptoUtils.DebugLog("Corruption detected in database " + fDb.getAbsolutePath() + " while trying to encrypt it"),
            new LicenseHook());
    int nVersion = db.getVersion();
    db.rawExecSQL("ATTACH DATABASE '" + fDbEncrypted.getAbsolutePath() + "' AS encrypted KEY \"" + sDbPassword + "\"");
    db.rawExecSQL("SELECT sqlcipher_export('encrypted')");
    db.rawExecSQL("DETACH DATABASE encrypted");
    CryptoUtils.closeSafely(db);
    setDatabaseVersion(fDbEncrypted, sDbPassword, nVersion);
    CryptoUtils.deleteFile(fDb);
    CryptoUtils.moveFile(fDbEncrypted, fDb);
}

I've revised the changelogs of SQLite from version 3.45.3 to 3.49.1. Many index-related optimizations have been added, so I assume some of them must be at work here.