Skip to content
This repository has been archived by the owner on Oct 11, 2024. It is now read-only.

Better utilize database indexes to improve performance #910

Open
albrow opened this issue Aug 14, 2020 · 0 comments
Open

Better utilize database indexes to improve performance #910

albrow opened this issue Aug 14, 2020 · 0 comments
Labels
db Issues related to the db package performance Related to improving or measuring performance

Comments

@albrow
Copy link
Contributor

albrow commented Aug 14, 2020

Our database implementations for both SQL and Dexie.js are only as optimized as they need to be for current usage/demand of Mesh. However, there are a few known areas where would could make further optimizations if needed in the future.

For SQL, we do not currently use any indexes (except for primary keys). Indexes can improve query performance by reducing the number of disk reads required. However, indexes also have an up front cost since they make every write slightly slower. Care should be taken to ensure that we only create indexes as needed, and that the indexes we create actually do improve performance in a meaningful way. We should use the slow query logs (mentioned in #909) as an indicator for which common queries could be improved by the addition of indexes.

For Dexie.js, the current situation is a bit different. Because Dexie.js does not allow running queries on unindexed fields, every field is currently indexed. However, we can't always utilize the indexes because of some restrictions on how Dexie.js works. For example, Table.where only accepts one index/one filter and cannot operate on multiple indexes at once. For queries with multiple filters, we currently run the query in-memory. This is not efficient for queries that return a large number of records.

There are a few steps we could take to improve Dexie.js performance as needed:

  1. For queries with more than one filter, we could use the first filter as the criteria for Table.where and then do further filtering in-memory. This is actually mentioned in a comment.
  2. For common, performance-critical queries with multiple filters, we can in some cases use compound indexes to run the entire query without filtering in-memory. An example of one place where we already use this approach is the [isNotPinned+expirationTimeSeconds] index introduced in Re-implement dynamic max expiration time #832.
@albrow albrow added db Issues related to the db package performance Related to improving or measuring performance labels Aug 14, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
db Issues related to the db package performance Related to improving or measuring performance
Projects
None yet
Development

No branches or pull requests

1 participant