What methods of database query optimization can you list?

• Indexing: Creating indexes for frequently used columns in WHERE, JOIN, and ORDER BY clauses.
• Query optimization:
  • Using EXPLAIN/EXPLAIN ANALYZE to analyze the query execution plan.
  • Avoiding SELECT *, explicitly specifying needed columns.
  • Optimizing JOIN operations by choosing the appropriate join type.
  • Minimizing the use of functions in WHERE clause.
  • Avoiding subqueries when JOIN can be used.
  • Optimizing GROUP BY and ORDER BY statements.
• Denormalization: In controlled cases, duplicating data to reduce the number of JOIN operations.
• Partitioning: Dividing large tables into smaller, manageable parts.
• Caching: Caching results of frequently executed queries at the application or database level.
• Database tuning: Optimizing database server configuration parameters (e.g., RAM, disks, caches).
• Limiting results: Using LIMIT to retrieve only the necessary number of records.
• Using ORM tools: Configuring ORM (e.g., Hibernate) to generate optimal SQL queries (lazy/eager loading, caching).

-- Example of query analysis
EXPLAIN SELECT customer_name FROM customers WHERE customer_id = 123;