Can you explain the purpose of the EXPLAIN command in SQL and how it helps in query optimization?

The EXPLAIN command in SQL is used to analyze the execution plan of a database query. It shows how the DBMS will execute the query: which indexes will be used, the order of table scans, join types, etc.

This helps optimize queries by identifying bottlenecks, such as:

• Lack of indexes, leading to full table scans.
• Incorrect order of table joins.
• Excessive sorting or filtering operations.

Example of usage:

EXPLAIN SELECT * FROM users WHERE age > 30;

The result will show how the DBMS plans to execute this query, allowing decisions to be made about adding indexes or rewriting the query to improve performance.