Android SQLite Select查询速度

Question

我想帮助加快我的 SQLite 数据库中一个相当简单的选择查询。

查询正在搜索一个包含大约 6500 行的表，目前返回时间约为 600 毫秒。我不知道这是否可以接受，但我曾希望它会比这更快。我最终会将其放入 AsyncTask 中，但希望尽可能优化。在下面的代码 fragment 中，您可以看到我的尝试。
KEY_songPath 已编入索引。

public ArrayList<Song> getCurrentDirSongs(String currentDir) {
long startTime = System.currentTimeMillis();
ArrayList<Song> songsList = new ArrayList<Song>();

String selectQuery = "";
int strLength = currentDir.length();
    //selectQuery = "SELECT  * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " >= '" + currentDir + "')";
    //selectQuery = "SELECT  * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " GLOB '" + currentDir + "*')";
    //selectQuery = "SELECT  * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " LIKE '" + currentDir + "%')";
    selectQuery = "SELECT  * FROM " + TABLE_SONGS + " WHERE " + KEY_ID +
                    " IN (SELECT " + KEY_ID +" FROM "+ TABLE_SONGS + " WHERE length(" + KEY_songPath + ")>= "+ strLength +") AND " + 
                    "(" + KEY_songPath + " LIKE '" + currentDir + "%')";

SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.rawQuery(selectQuery, null);

// looping through all rows and adding to list
if (cursor.moveToFirst()) {
    do {
        Song song = new Song();
        song.setSongTitle(cursor.getString(1));
        song.setSongPath(cursor.getString(2));
        song.setSongArtist(cursor.getString(3));
        song.setSongAlbum(cursor.getString(4));
        song.setTrackNumber(cursor.getString(5));
        song.setID(Integer.parseInt(cursor.getString(6)));
        song.setAlbumID(Integer.parseInt(cursor.getString(7)));
        song.setArtistID(Integer.parseInt(cursor.getString(8)));
        song.setSongCheckedStatus(Boolean.parseBoolean(cursor.getString(9)));
        song.setSongPosition(Integer.parseInt(cursor.getString(10)));
        song.setSongDuration(Integer.parseInt(cursor.getString(11)));
        // Adding song to list
        songsList.add(song);
    } while (cursor.moveToNext());
}
long estimatedTime = System.currentTimeMillis() - startTime;
Log.d("AMCDatabase Database Handler: ", "SongsList.size(): "+ getSongsCount("SongsList") + " Time: " + estimatedTime + "ms");
// return contact list
return songsList;

返回结果如下:SongsList.size():6390 时间:588ms

感谢收到的任何帮助!

P.S 这可能都不是问题 - 是否可以接受 1 秒的时间查询和 6000 个值到 ArrayList？

Answer 1

尽量不要使用嵌套的 SELECT 语句，而是看看是否可以使用 JOIN。

例子:

SELECT * FROM Songs AS s1 JOIN Songs AS s2 ON s1.KEY_ID = s2.KEY_ID WHERE length (s2.KEY_SONGPATH) >= " + strLength + ") AND s1.KEY_SONGPATH LIKE '" + currentDir + "%'