What operations are undesirable on a large table with tens of millions of records? How to optimize getting the COUNT of products on sale from a very large table?

Operations to avoid on very large tables (tens of millions of records):

• Full Table Scans — for example, SELECT * without indexes.
• Bulk updates or deletes without filtering — UPDATE or DELETE without WHERE can lock the table and take a long time.
• Aggregations without indexes — COUNT(*) or COUNT(column) without suitable indexes lead to long scans.

To optimize counting the number of products in a promotion from a large table:

1. Use indexes on columns involved in filters (e.g., promotion_id).
2. Maintain aggregated data in a separate table (materialized view or cache), updated upon changes.
3. Use table partitioning by date or other criteria to reduce the amount of data to scan.
4. Use specialized counters or in-memory counters (Redis, Memcached) for quick access.

Example of a query with an index:

CREATE INDEX idx_promo_id ON products(promo_id);

SELECT COUNT(*) FROM products WHERE promo_id = ?;

If the query is still slow, consider storing the count of products in a promotion in a separate table and updating it upon changes.