Adding indexes can provide significant performance benefits. However, the most effective SQL optimization for a relational database is to eliminate the need to execute the SQL statement completely. For a highly tuned application, the greatest amount of time for the total execution of the statement is the network overhead.
Removing SQL statements can reduce the application processing time. Additional steps necessary for each SQL statement include parsing, permission security checks, and generation of the query execution plan.
These are all overheads that add unnecessary load to the database server when statements are unnecessary. You can use the profiling functionality to get detailed timing of steps within the execution of a query.
Here is an example:
mysql> show profile source for query 7;
The Status values listed can be misleading, as these expose internal comments not originally intended for public view. Reviewing the actual MySQL source code file and line as specified is recommended to understand the full description.
There are several things you can do to optimize performance in addition to adding indexes:
- Eliminating SQL statements by removing duplicate, repeating or unneccesary SQL statements.
- Caching SQL statement results.
- Simplifying SQL statements.
Read on to learn more about these techniques.