Some of the optimization techniques in MySQL are as follows:
- Range Optimization: In this case we can use a single index to retrieve the subset of data from a table. Now the server has to work on less amount of data.
- Index Optimization: In this method several range scans can be merged into one result. So index scans from same table can be merged into one dataset.
- Engine Condition Pushdown Optimization: When we have a direct comparison between a non-indexed column and constant, MySQL optimizer will push the condition to storage engine for evaluation.
- Index Condition Pushdown Optimization: This is another optimization for selecting rows from a table by using an index.