What criteria will you use to decide which indexes to add to a large, actively used table?

When choosing indexes for a large actively used table, I consider the following criteria:

• Query analysis: I look at which fields are most frequently used in WHERE, JOIN, ORDER BY, and GROUP BY conditions.
• Column cardinality: indexes are more effective on columns with high uniqueness of values.
• Query types: B-Tree indexes are suitable for range searches, while special index types are used for full-text search.
• Performance profiling: I use EXPLAIN and profilers to understand which queries are slow.
• Balance between read and write: indexes speed up reading but slow down inserts and updates, so not everything should be indexed.

For example, if a query like

SELECT * FROM orders WHERE customer_id = ? AND order_date > ?;

is frequently executed, it makes sense to create a composite index on (customer_id, order_date).

It is also important to periodically review indexes, as the load and query patterns can change.