How can the selection from a table with 100 million rows be accelerated? Why can't indexes be simply added to all fields?

There are several ways to speed up a query from a table with 100 million rows:

• Use indexes only on the fields involved in WHERE conditions, JOINs, or sorting. Indexes allow quick access to the needed records without full table scans.
• Apply table partitioning based on logical criteria (e.g., by date) so that queries process only the relevant partitions.
• Cache frequently requested data.
• Use denormalization or materialized views to accelerate complex queries.
• Limit the result set (LIMIT) and use pagination.

Why can't you just add indexes to all fields:

• Indexes occupy additional space on disk and in memory.
• During insertions, updates, and deletions, indexes require extra work, slowing down write operations.
• Redundant indexes can degrade performance if the database engine chooses inefficient execution plans.
• Not all fields are suitable for indexing (e.g., fields with high cardinality or frequently changing fields).

Therefore, indexes should be added thoughtfully, analyzing real queries and load.