Speed up select query in sqlite - Android

Question

My data base class is bellow :

public class DataBaseInstagram extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 7;
    private static final String DATABASE_NAME = "Instagram";
    private static final String TABLE_PRODUCTS = "DetailImage";
    private static final String COLUMN_IMAGE_NAME = "imageName";
    private static final String COLUMN_IMAGE_ID = "imageID";

    public DataBaseInstagram(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_PRODUCTS_TABLE =
                "CREATE TABLE " + TABLE_PRODUCTS + "(" + COLUMN_IMAGE_NAME + " TEXT UNIQUE," + COLUMN_IMAGE_ID + " TEXT UNIQUE" + ")";
        db.execSQL(CREATE_PRODUCTS_TABLE);
        String INDEX = "CREATE UNIQUE INDEX products_index ON "
                + TABLE_PRODUCTS + " (imageName, imageID)";
        db.execSQL(INDEX);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }

    public List<ExistImage> select() {
        String sql = "select * from " + TABLE_PRODUCTS;
        SQLiteDatabase db = this.getReadableDatabase();
        List<ExistImage> lstExistImages = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            do {
                ExistImage existImage = new ExistImage();
                existImage.setImageName(cursor.getString(0));
                existImage.setImageID(cursor.getString(1));
                lstExistImages.add(existImage);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return lstExistImages;
    }
}

I use INDEX in my table .Will my select queries become faster?

Answer 1

short answer: an index may make the loading faster but this will not change your problem of slow loading.

Your app is loading data into an array so i assume that you are using an arrayadapter to fill a list or grid or spinner.

using a cursoradapter instead of an arrayadapter will make your app faster if you have many items in the query.

an array adapter has to load all items.

a curosadapter load as many items as are currently visible. (load on demand)

I have an android database app with 18 000 items that are shown in a grid using a cursoradapter und it is quite fast..