Indexes
An index is another structure the database maintains to help locate or order data. It improves some reads and costs space and write work.
Selectivity
A predicate matching very few rows may benefit from an index. A status column with only a few values may be less selective, but the workload and distribution still matter.
Composite order
An index on (customer_id, order_date) can support customer-based filtering and ordered access within a customer. Leading-column order matters; it is not equivalent to (order_date, customer_id).
Covering indexes
An index can cover a query when it contains the needed data without an extra table lookup. SQL Server supports INCLUDE columns; SQLite uses ordinary indexed columns and does not support that INCLUDE syntax.
Write and maintenance trade-offs
INSERT, UPDATE and DELETE may maintain several indexes. Avoid indexing every column by default. Test the intended query and watch for redundant indexes, storage growth and maintenance cost.