How have you optimized queries? How did you realize they were performing poorly?

Query optimization begins with monitoring their performance: if queries run for a long time, cause locks, or heavily load the database, it is a signal for optimization.

To understand the problems, I used:

• Execution plan analysis (EXPLAIN, EXPLAIN ANALYZE)
• Logging slow queries
• Monitoring the database server load

Optimization methods:

• Adding or adjusting indexes to speed up searches
• Rewriting queries to reduce the number of JOINs or subqueries
• Using aggregate functions and filters on the database side
• Caching results of frequently executed queries

Example: if a query with JOINs across multiple tables takes a long time, you can check if there are indexes on the join keys and add them if necessary.