Query Support in Android Room Database

Question

I am using Room database in my Android Application.

Now, there was a need to use lag() and over() functions in query I have used.

But since it was not supported in Room, I have made sub-query as below :

SELECT DISTINCT strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, e.name " +
            "FROM ( " +
            "SELECT b1.*, " +
            "( " +
            "SELECT b2.status " +
            "FROM batteryDetails b2 " +
            "WHERE b2.batteryId = b1.batteryId AND b2.timeStamp < b1.timeStamp " +
            "ORDER BY b2.timeStamp DESC LIMIT 1) AS oldStatus " +
            "FROM batteryDetails b1) batteryDetails " +
            "LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status " +
            "WHERE oldStatus <> status " +
            "AND batteryId= :batteryId " +
            "AND (timeStamp between :startDate AND :endDate) " +
            "group by timeStamp order by timeStamp DESC

But this is taking too much time for some large amount of data.

Are lag() and over() functions are really not supported in Room database android? I am using latest version of Room. Can I make this in more efficient manner?

Answer 1

Are lag() and over() functions are really not supported in Room database android?

It's not really Room, the limitation is the SQLite bundled with the device which should be at a minimum level according to the Android API, and that is always behind the available SQLite. It is the installed SQLite (I believe) that determines what functionality is available. That is I believe that Room just passes the SQL to SQLite and it (the installed SQLite) ascertains whether or not it is valid/invalid SQL.

Can I make this in more efficient manner?

Indexes

Is the batteryId column of the batteryDetails table indexed? If not then adding such an index could reduce the time.

• Based upon testing using 1000000 rows (as below), without an index the time was approx. 45 seconds, with the index approx. 13 seconds.

Triggers

However, you may wish to consider utilising a Trigger, to do away with the need to ascertain a change in status via a subquery (if I interpreted you SQL correctly).

Assuming that the batteryDetails table's batteryId is a reference to a battery then you could add a column to the battery table that holds the last battery status. When a batteryDetails row is inserted, then it could obtain the status and save it in a batteryDetails column (e.g. previousStatus) via the trigger, the trigger could then update the battery's last status with the status being inserted via the same trigger.

• Note that the burden, as such, is passed to the insert, that is the inserts will have an overhead, so if you are inserting batteryDetails very frequently this may be an issue.

• as can be seen the inserts with the trigger took twice as long (2 seconds instead of 1).

Testing this, then the equivalent query, for the 1000000 row table is 1/10th of a second.

Testing SQL (used Navicat but could be copied to any equivalent SQLite Tool) :-

DROP TABLE IF EXISTS battery;
DROP TABLE IF EXISTS batteryDetails;
DROP TABLE IF EXISTS eventTypes;
CREATE TABLE IF NOT EXISTS batteryDetails (batteryId INTEGER, timestamp INTEGER, status INTEGER);
CREATE TABLE IF NOT EXISTS eventTypes (eventType INTEGER PRIMARY KEY, name TEXT);
/* Some add event types */
INSERT INTO eventTypes VALUES (1,'flat (less than 10%)'),(2,'low (10%-50%)'),(3,'medium (50%-75%)'),(4,'full (75%-100%)');
/* Load some testing data */
WITH 
RECURSIVE i(counter) AS (SELECT 1 UNION ALL SELECT counter + 1 FROM i LIMIT 1000000) 
    INSERT INTO batteryDetails SELECT abs(random() % 100)+ 1,strftime('%s','now') - counter,(abs(random() % 4)) + 1 FROM i
;

/* The original Query (with a little extra output)
    rather calculated value have been used for start and end
    the battery ID is one with the 100 batteries
*/
SELECT DISTINCT
    rowid,
    strftime('%s','now','-2 day') AS starting,
    strftime('%s','now','-1 day') AS ending,
    strftime('%Y-%m-%d','now','-2 day') AS startingYYMMDD,
    strftime('%Y-%m-%d','now','-1 day') AS endingYYMMDD,
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM (
        SELECT 
            rowid,
            b1.*,(
                SELECT b2.status 
                FROM batteryDetails b2 
                WHERE b2.batteryId = b1.batteryId 
                    AND b2.timeStamp < b1.timeStamp 
                ORDER BY b2.timeStamp DESC LIMIT 1
            ) AS oldStatus 
        FROM batteryDetails b1
    ) batteryDetails 
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE oldStatus <> status 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
;

/* Adding Index if not already indexed */
CREATE INDEX idx_batteryDetails_batteryId ON batteryDetails (batteryId);
/* use the original query again but with the indexed batteryId column */
SELECT DISTINCT
    rowid,
    strftime('%s','now','-2 day') AS starting,
    strftime('%s','now','-1 day') AS ending,
    strftime('%Y-%m-%d','now','-2 day') AS startingYYMMDD,
    strftime('%Y-%m-%d','now','-1 day') AS endingYYMMDD,
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM (
        SELECT 
            rowid,
            b1.*,(
                SELECT b2.status 
                FROM batteryDetails b2 
                WHERE b2.batteryId = b1.batteryId 
                    AND b2.timeStamp < b1.timeStamp 
                ORDER BY b2.timeStamp DESC LIMIT 1
            ) AS oldStatus 
        FROM batteryDetails b1
    ) batteryDetails 
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE oldStatus <> status 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
;
/* Now going try trigger */
DROP TABLE IF EXISTS Battery;
DROP TABLE IF EXISTS BatteryDetails;
DROP TRIGGER IF EXISTS setPreviousStatus;
/* Create the battery table with the lastStatus column (THE CRUX of the technique) */
CREATE TABLE IF NOT EXISTS Battery (batteryId INTEGER PRIMARY KEY, lastStatus DEFAULT 1);
/* Add the batteries to the battery table (id's 1 to 100) defaulting to last status of flat*/
WITH RECURSIVE b(id) AS (SELECT 1 UNION ALL SELECT id+1 FROM b LIMIT 100)
    INSERT INTO battery SELECT id,1 FROM b
;
/* Create the modified batteryDetails table i.e. adding the previousStatus column */
CREATE TABLE IF NOT EXISTS batteryDetails (batteryId INTEGER, timestamp INTEGER, status INTEGER, previousStatus INTEGER);
/* Create the TRIGGER to 
 1. apply the last status of the battery to the newly inserted batteryDetails row
 2. update the battery to the new status
 NOTE ROOM doesn't cater for TRIGGERS via annotation you would need to add it (add a callback and do in onCreate)
 */
CREATE TRIGGER IF NOT EXISTS setPreviousStatus AFTER INSERT ON batteryDetails
    BEGIN
        UPDATE batteryDetails SET previousStatus = (SELECT laststatus FROM battery WHERE batteryId = new.batteryId) WHERE rowid = new.rowid;
        UPDATE battery SET lastStatus = new.status WHERE batteryId = new.batteryId;
    END
;
/*
Add some data again BUT now with previousStatus column (set to null, it wil/ be updated by the trigger)
*/
WITH 
RECURSIVE i(counter) AS (SELECT 1 UNION ALL SELECT counter + 1 FROM i LIMIT 1000000) 
    INSERT INTO batteryDetails SELECT abs(random() % 100)+ 1,strftime('%s','now') - counter,(abs(random() % 4)) + 1,null FROM i
;

/* I believe the equivalent query that utilises the previousStatus column */
SELECT DISTINCT
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM batteryDetails
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE status <> previousStatus 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
;

/* For myself cleanup the testing environment (trigger and index should be deleted by SQLite)*/
DROP TABLE IF EXISTS battery;
DROP TABLE IF EXISTS batteryDetails;
DROP TABLE IF EXISTS eventTypes;

Example run (aka the message log along with timings) :-

DROP TABLE IF EXISTS battery
> OK
> Time: 0.282s


DROP TABLE IF EXISTS batteryDetails
> OK
> Time: 1.309s


DROP TABLE IF EXISTS eventTypes
> OK
> Time: 0.299s


CREATE TABLE IF NOT EXISTS batteryDetails (batteryId INTEGER, timestamp INTEGER, status INTEGER)
> OK
> Time: 0.084s


CREATE TABLE IF NOT EXISTS eventTypes (eventType INTEGER PRIMARY KEY, name TEXT)
> OK
> Time: 0.096s


/* Some add event types */
INSERT INTO eventTypes VALUES (1,'flat (less than 10%)'),(2,'low (10%-50%)'),(3,'medium (50%-75%)'),(4,'full (75%-100%)')
> Affected rows: 4
> Time: 0.149s


/* Load some testing data */
WITH 
RECURSIVE i(counter) AS (SELECT 1 UNION ALL SELECT counter + 1 FROM i LIMIT 1000000) 
    INSERT INTO batteryDetails SELECT abs(random() % 100)+ 1,strftime('%s','now') - counter,(abs(random() % 4)) + 1 FROM i
> Affected rows: 1000000
> Time: 0.847s


/* The original Query (with a little extra output)
    rather calculated value have been used for start and end
    the battery ID is one with the 100 batteries
*/
SELECT DISTINCT
    rowid,
    strftime('%s','now','-2 day') AS starting,
    strftime('%s','now','-1 day') AS ending,
    strftime('%Y-%m-%d','now','-2 day') AS startingYYMMDD,
    strftime('%Y-%m-%d','now','-1 day') AS endingYYMMDD,
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM (
        SELECT 
            rowid,
            b1.*,(
                SELECT b2.status 
                FROM batteryDetails b2 
                WHERE b2.batteryId = b1.batteryId 
                    AND b2.timeStamp < b1.timeStamp 
                ORDER BY b2.timeStamp DESC LIMIT 1
            ) AS oldStatus 
        FROM batteryDetails b1
    ) batteryDetails 
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE oldStatus <> status 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
> OK
> Time: 47.66s


/* Adding Index if not already indexed */
CREATE INDEX idx_batteryDetails_batteryId ON batteryDetails (batteryId)
> OK
> Time: 1.01s


/* use the original query again but with the indexed batteryId column */
SELECT DISTINCT
    rowid,
    strftime('%s','now','-2 day') AS starting,
    strftime('%s','now','-1 day') AS ending,
    strftime('%Y-%m-%d','now','-2 day') AS startingYYMMDD,
    strftime('%Y-%m-%d','now','-1 day') AS endingYYMMDD,
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM (
        SELECT 
            rowid,
            b1.*,(
                SELECT b2.status 
                FROM batteryDetails b2 
                WHERE b2.batteryId = b1.batteryId 
                    AND b2.timeStamp < b1.timeStamp 
                ORDER BY b2.timeStamp DESC LIMIT 1
            ) AS oldStatus 
        FROM batteryDetails b1
    ) batteryDetails 
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE oldStatus <> status 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
> OK
> Time: 13.525s


/* Now going try trigger */
DROP TABLE IF EXISTS Battery
> OK
> Time: 0s


DROP TABLE IF EXISTS BatteryDetails
> OK
> Time: 1.925s


DROP TRIGGER IF EXISTS setPreviousStatus
> OK
> Time: 0s


/* Create the battery table with the lastStatus column (THE CRUX of the technique) */
CREATE TABLE IF NOT EXISTS Battery (batteryId INTEGER PRIMARY KEY, lastStatus DEFAULT 1)
> OK
> Time: 0.243s


/* Add the batteries to the battery table (id's 1 to 100) defaulting to last status of flat*/
WITH RECURSIVE b(id) AS (SELECT 1 UNION ALL SELECT id+1 FROM b LIMIT 100)
    INSERT INTO battery SELECT id,1 FROM b
> Affected rows: 100
> Time: 0.083s


/* Create the modified batteryDetails table i.e. adding the previousStatus column */
CREATE TABLE IF NOT EXISTS batteryDetails (batteryId INTEGER, timestamp INTEGER, status INTEGER, previousStatus INTEGER)
> OK
> Time: 0.216s


/* Create the TRIGGER to 
 1. apply the last status of the battery to the newly inserted batteryDetails row
 2. update the battery to the new status
 NOTE ROOM doesn't cater for TRIGGERS via annotation you would need to add it (add a callback and do in onCreate)
 */
CREATE TRIGGER IF NOT EXISTS setPreviousStatus AFTER INSERT ON batteryDetails
    BEGIN
        UPDATE batteryDetails SET previousStatus = (SELECT laststatus FROM battery WHERE batteryId = new.batteryId) WHERE rowid = new.rowid;
        UPDATE battery SET lastStatus = new.status WHERE batteryId = new.batteryId;
    END
> Affected rows: 100
> Time: 0.086s


/*
Add some data again BUT now with previousStatus column (set to null, it wil/ be updated by the trigger)
*/
WITH 
RECURSIVE i(counter) AS (SELECT 1 UNION ALL SELECT counter + 1 FROM i LIMIT 1000000) 
    INSERT INTO batteryDetails SELECT abs(random() % 100)+ 1,strftime('%s','now') - counter,(abs(random() % 4)) + 1,null FROM i
> Affected rows: 1000000
> Time: 1.907s


/* I believe the equivalent query that utilises the previousStatus column */
SELECT DISTINCT
    strftime('%Y-%m-%d',datetime(DISTINCT timeStamp, 'unixepoch')) AS eventAtDate, 
    e.name 
    FROM batteryDetails
    LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
    WHERE status <> previousStatus 
        AND batteryId= 10 
        AND (timeStamp between strftime('%s','now','-2 day') AND strftime('%s','now','-1 day'))
    GROUP BY timeStamp 
    ORDER BY timeStamp DESC
> OK
> Time: 0.092s


/* For myself cleanup the testing environment (trigger and index should be deleted by SQLite)*/
DROP TABLE IF EXISTS battery
> OK
> Time: 0.091s


DROP TABLE IF EXISTS batteryDetails
> OK
> Time: 1.442s


DROP TABLE IF EXISTS eventTypes
> OK
> Time: 0.101s

• Note the data above is obviously contrived (much is randomly generated) to perhaps reflect the real data and some assumptions have been made.

---

Applying the above in Room (Using the TRIGGER)

The Entities (Battery, EventType and BatteryDetail) :-

Battery :-

@Entity(tableName = TABLENAME)
data class Battery(
    @PrimaryKey
    @ColumnInfo(name = ID_COLUMN)
    var id: Long? = null,
    var lastStatus: Int = 1
) {
    companion object {
        const val TABLENAME = "battery"
        const val ID_COLUMN = "batteryId"
        const val LASTSTATUS_COLUMN = "lastStatus"
    }
}

• NOTE I strongly recommend constants for table component names (e.g. especially for generating the TRIGGER to eliminate typos)

EventType :-

@Entity(tableName = TABLENAME)
data class EventType(
    @PrimaryKey
    @ColumnInfo(name = EVENTTYPE_COLUMN)
    var eventType: Long? = null,
    @ColumnInfo(name = EVENTTYPE_NAME_COLUMN)
    var name: String
) {
    companion object {
        const val TABLENAME = "eventTypes"
        const val EVENTTYPE_COLUMN = "eventType"
        const val EVENTTYPE_NAME_COLUMN = "name"
    }
}

BatteryDetail (pretty long winded though) :-

@Entity(
    tableName = TABLENAME,
    indices = [
        Index(value = [TIMESTAMP_COLUMN]),
        Index(value = [BatteryDetail.BATTERY_ID_COLUMN])
    ],
    foreignKeys = [
        ForeignKey(
            entity = Battery::class,
            parentColumns = [Battery.ID_COLUMN],
            childColumns = [BatteryDetail.BATTERY_ID_COLUMN],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class BatteryDetail(
    @PrimaryKey @ColumnInfo(name = ID_COLUMN)
    var id: Long? = null,
    @ColumnInfo(name = BATTERY_ID_COLUMN)
    var batteryId: Long,
    @ColumnInfo(name = TIMESTAMP_COLUMN)
    var timestamp: Long = System.currentTimeMillis() / 1000,
    @ColumnInfo(name = STATUS_COLUMN)
    var status: Int,
    @ColumnInfo(name = PREVSIOUS_STATUS_COLUMN)
    var previousStatus: Int = -1
) {
    companion object {
        const val TABLENAME = "batteryDetails"
        const val ID_COLUMN: String = "id"
        const val BATTERY_ID_COLUMN = "batteryId"
        const val TIMESTAMP_COLUMN = "timestamp"
        const val STATUS_COLUMN = "status"
        const val PREVSIOUS_STATUS_COLUMN = "previousStatus"

        /*
            CREATE TRIGGER IF NOT EXISTS setPreviousStatus AFTER INSERT ON batteryDetails
        BEGIN
        UPDATE batteryDetails SET previousStatus = (SELECT laststatus FROM battery WHERE batteryId = new.batteryId) WHERE rowid = new.rowid;
        UPDATE battery SET lastStatus = new.status WHERE batteryId = new.batteryId;
        END;
         */
        const val TRIGGER_NAME = "trigger_" + BatteryDetail.TABLENAME + "_afterinsert"
        const val TRIGGER_CREATE_SQL =
            "CREATE TRIGGER IF NOT EXISTS " +
                    TRIGGER_NAME  +
                    " AFTER INSERT ON " + BatteryDetail.TABLENAME +
                    " BEGIN " +
                    /* 1st apply the last battery status to the previous status column */
                    " UPDATE " + BatteryDetail.TABLENAME + " SET " + BatteryDetail.PREVSIOUS_STATUS_COLUMN +
                    " = (" +
                    "SELECT " + Battery.LASTSTATUS_COLUMN + " FROM " + Battery.TABLENAME +
                    " WHERE " + Battery.ID_COLUMN + " = new." + BatteryDetail.BATTERY_ID_COLUMN +
                    ") WHERE rowid = new.rowid;" +
                    /* 2nd Update the battery status with the new status */
                    " UPDATE " + Battery.TABLENAME + " SET " + Battery.LASTSTATUS_COLUMN + " = new." + BatteryDetail.STATUS_COLUMN +
                    " WHERE " + Battery.ID_COLUMN + " = new." + BatteryDetail.BATTERY_ID_COLUMN + ";" +
                    "END;"
        const val TRIGGER_DROP_SQL = " DROP TRIGGER IF EXISTS $TRIGGER_NAME;"
    }
}

A POJO for the result of the query in question EventStatusChange :-

data class EventStatusChange(
    var eventAtDate: String,
    var name: String
)

An @Dao class AllDao :-

@Dao
abstract class AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(battery: Battery): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(eventType: EventType): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(batteryDetail: BatteryDetail): Long
    @Query(
        "SELECT DISTINCT " +
                "strftime('%Y-%m-%d',datetime(DISTINCT timestamp,'unixepoch')) AS eventAtDate, " +
                "e.name FROM batteryDetails " +
                "LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status " +
                "WHERE status <> previousStatus " +
                "AND batteryId=:batteryId " +
                "AND timestamp BETWEEN :start AND :end " +
                "GROUP BY timestamp " +
                "ORDER BY timestamp DESC;"
    )
    abstract fun getEventChanges(batteryId: Long, start: Long, end: Long): List<EventStatusChange>
}

The @Database class TheDatabase - creates the trigger :-

@Database(entities = [Battery::class,EventType::class,BatteryDetail::class], version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDao(): AllDao

    companion object {
        @Volatile
        private var instance: TheDatabase? = null
        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(
                    context,
                    TheDatabase::class.java,
                    "battery.db"
                )
                    .allowMainThreadQueries()
                    .addCallback(cb())
                    .build()
            }
            return instance as TheDatabase
        }

        class cb: Callback() {
            override fun onCreate(db: SupportSQLiteDatabase) {
                super.onCreate(db)
                Log.d("DB_ONCREATE","Dropping and create trigger ${BatteryDetail.TRIGGER_NAME}")
                db.execSQL(BatteryDetail.TRIGGER_DROP_SQL)
                db.execSQL(BatteryDetail.TRIGGER_CREATE_SQL)
            }
        }
    }
}

• Note for brevity/convenience .allowMainThreadQueries has been used.

Putting it altogether in an Activiy MainActivity :-

class MainActivity : AppCompatActivity() {

    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        val r = Random
        db = TheDatabase.getInstance(this)
        db.openHelper.writableDatabase
        dao = db.getAllDao()
        val batterycount: Int = 10
        val eventTypeCount: Int = 4
        val intervalInSeconds = 10
        val day = 24 * 60 * 60
        val baseStartTime = (System.currentTimeMillis() / 1000) - (day * 2)
        for(i in 1..batterycount) {
            dao.insert(Battery(id = i.toLong()))
        }
        for (i in 1..eventTypeCount) {
            dao.insert(EventType(name = "Type${i}", eventType = i.toLong()))
        }
        for (i in 1..100) {
            for(ii in 1..batterycount) {
                var e_type: Int = r.nextInt(eventTypeCount - 1) + 1
                dao.insert(BatteryDetail(timestamp = baseStartTime + (i * intervalInSeconds ),batteryId = ii.toLong(),status = e_type))
            }
        }
        for(esc: EventStatusChange in dao.getEventChanges(5,1632291539, 1632291549)) {
            Log.d("DBINFO","Date = ${esc.eventAtDate} Name = ${esc.name}")
        }
    }
}

• Loads data, the batteryDetail using a random status.
• and then extracts using the said query (for battery 5 and some timestamps (I cheated by looking at the actual data to get the values)). The output to the log

:-

2021-09-24 16:23:36.090 D/DBINFO: Date = 2021-09-22 Name = Type2
2021-09-24 16:23:36.090 D/DBINFO: Date = 2021-09-22 Name = Type3

• Not saying that this is correct, the data is random so not that easy to check. However, it does prove that the code works (even if not as required). I'd suggest that you utilise data where you know the result(s) that you want.

The data in the database via Android Studio's App Inspection :-

EventTypes

Batteries

BatteryDetails (half of the 50 rows):-

Some other considerations

using a tool such as Navicat (DB Browser for SQLite and others) can make you life a lot easier rather. For optimising then consider preceding a query with EXPLAIN QUERY PLAN and EXPLAIN (the query doesn't run instead output is produced that may help optimise).

You may find the following link (and links embedded) of use https://sqlite.org/eqp.html