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. INSERT INTO table VALUES (.) The first form (with the 'VALUES' keyword) creates one or more new rows in an existing table. 4 Answers Sorted by: 30 You can append a new collation to your select query to find case sensitive or insensitive. 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). upsert-clause: The INSERT statement comes in three basic forms. In SQLite you can retrieve rows from a table in either ascending or descending order. 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. 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. For more information on indexing in SQLite. 42 minutes to read 7 contributors Feedback In this article Summary High-impact changes Medium-impact changes Low-impact changes The following API and behavior changes have the potential to break existing applications when upgrading them to 3.x. 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. The SQLite ORDER BY clause can be used without specifying the ASC or DESC modifier. 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. 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).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |