0

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:

  • timestamp
  • threadId
  • (threadId, timestamp)
1
  • 4
    Index on (currentuser ID, threadid, timestamp) – ypercubeᵀᴹ Mar 7 at 10:27
1

As ypercube mentioned, you'll want to create your index on all three fields, currentUserId, threadId and timestamp like so: CREATE INDEX IX_YourIndexName ON message (currentUserId, threadId, timestamp). The reasoning being you should index on all fields in your predicates (JOIN, WHERE, and HAVING clauses) to create a fully covering index (see 1.7 Covering Indices) which would be the currentUserId and threadId in this case. And then also adding the fields from your ORDER BY clause will help sort the index on those fields as well which will reduce the work for sorting when your query runs. For more information on indexing in SQLite.

Note you might even see a slight performance benefit by switching around the order of currentUserId and threadId in the index definition, assuming the threadId is more unique than the currentUserId in the table. By defining your indexes with the most unique fields first, it improves the selectivity of that index because less values need to be seeked on upfront. So if threadId is more unique of a field, you could try this index definition as well CREATE INDEX IX_YourIndexName ON message (threadId, currentUserId, timestamp). But unless the difference in uniqueness between threadId and currentUserId is a lot, or you have a lot of data, you probably won't notice much of a difference, so either definition should be equally fine.

13
  • First: If my query doesn't have WHERE clause and only has ORDER BY. So I will need to create index on timestamp, right? Does an index help to speed up when sorting? – nhoxbypass Mar 7 at 14:32
  • Second: Does it different if I create an 3-columns index (currentUserId, threadId, timestamp), OR create two index (currentUserId, threadId) and `(timestamp). – nhoxbypass Mar 7 at 14:44
  • 1
    @nhoxbypass - no two datasets are ever identical - you can always test and see what works best with your own data. – Vérace Mar 7 at 15:21
  • 1
    @nhoxbypass If you changed your query to have no WHERE clause, the proposed index might help, but an additional index on just timestamp may be most optimal. As Verace mentions, testing will always give you the best answers. :) – J.D. Mar 7 at 18:33
  • 1
    @ypercubeᵀᴹ More often than not, the amount of data and the variance in uniqueness between the fields won't be enough for it to matter, as you've experienced, but I figured it's a good point to make people aware of as it is still true and occurs in larger datasets. I've experienced it with big data (specifically in Microsoft SQL Server) where the index on ColumnA, ColumnB resulted in it being seconds slower than on ColumnB, ColumnA for a simple SELECT on the table with both columns as predicates in the WHERE clause. This is because the seek on ColumnB eliminated a lot more... – J.D. Mar 8 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.