Learn how to optimize subscription queries with LIMIT and ORDER BY clauses to avoid performance issues
LIMIT
or LIMIT + ORDER BY
clauses. These queries maintain state about which documents are currently within
the limit boundary, which can lead to performance challenges when documents
change.
WHERE
clause) or sorting (ORDER BY
clause) on mutable fields when LIMIT
is used.Query | Why It Performs Well |
---|---|
SELECT * FROM orders LIMIT 50 | Cache invalidation only on document deletion |
SELECT * FROM orders WHERE _id.restaurantID = 1 LIMIT 50 | restaurantID is immutable, avoiding filter-based invalidation |
SELECT * FROM orders ORDER BY createdAt DESC LIMIT 50 | createdAt doesn’t change, preventing sort-based invalidation |
SELECT * FROM orders WHERE restaurantID = 1 ORDER BY createdAt DESC LIMIT 50 | Both filter and sort use fields that don’t change |
createdAt DESC
, new documents with larger timestamps are handled efficiently without cache invalidation through internal optimizations.Query | Performance Impact |
---|---|
SELECT * FROM orders WHERE !closed LIMIT 50 | Cache invalidates when closed status changes |
SELECT * FROM orders WHERE !closed ORDER BY createdAt ASC LIMIT 50 | Worse than above due to sort operation cost during invalidation |
SELECT * FROM orders WHERE !closed ORDER BY createdAt DESC LIMIT 50 | May perform better if new orders arrive faster than closures |
SELECT * FROM cars ORDER BY miles DESC LIMIT 50 | Depends on update frequency of miles field |
Query | Why It’s Problematic |
---|---|
SELECT * FROM orders WHERE !closed ORDER BY updatedAt LIMIT 50 | Both filter and sort use mutable fields that change together |