I have a table that store messages for my Chat app:
CREATE TABLE message ( currentUserId, threadId, timestamp, message, msgType, msgState )
Then I used this query to load 20 message for each thread:
SELECT rowid, * FROM message WHERE currentUserId = '123' AND threadId = '456' ORDER BY timestamp LIMIT 50, 20
So which index should I create to optimize loading speed of this query?
I already try to index in each of these approaches but none of them seem to be working:
- (threadId, timestamp)