SQLite performance optimization when using multiple indexes with a complex query
I'm converting an old project and I've been struggling with this for a few days now and could really use some help... I'm working with important performance degradation when executing a complex query on my SQLite database, which has multiple indexes set up. The query looks something like this: ```sql SELECT * FROM orders WHERE customer_id = ? AND order_date BETWEEN ? AND ? AND status IN ('completed', 'pending'); ``` I've indexed both `customer_id` and `order_date`, expecting the query to run efficiently. However, the execution time is noticeably slow, taking over 2 seconds even with a small dataset of around 10,000 records. The `EXPLAIN QUERY PLAN` output shows that it's using the indexes, but it still seems inefficient: ```plaintext 0|0|SEARCH TABLE orders USING INDEX orders_customer_id_idx (customer_id=? ) 0|1|SEARCH TABLE orders USING INDEX orders_order_date_idx (order_date BETWEEN ? AND ?) ``` I've tried updating my indexes and ensuring that they are correctly defined, but I still need to pinpoint what’s causing the slowdown. Additionally, I've experimented with using a composite index like this: ```sql CREATE INDEX idx_customer_date_status ON orders (customer_id, order_date, status); ``` However, this didn’t yield any noticeable improvement either. Is there a known limitation with how SQLite handles multiple indexes in a single query? Are there best practices for optimizing complex queries with potentially overlapping index usage? I'm using SQLite version 3.37.2. Any insights or recommendations would be greatly appreciated! Thanks for taking the time to read this! I'm using Sql 3.9 in this project. Could someone point me to the right documentation?