Describe the steps to optimize and improve performance of MySQL.myisamchk is used to get information about your database tables or to check, repair, or optimize them. This command can check or repair MyISAM tables. It can also optimize and analyze these tables. Database design, tuning of databases can be used to improve and optimize performance of MySql.
Describe how to increase the performance of MySQL select query.Indexes should be used to increase the performance of MySQl select query. Index on a column can be specified using col_name(n)
Here, n specifies the first “n” characters that can be used to create an index. Columns that will be used more often in a select query should be indexed. When a new index is created; MYSQl builds a separate block of information that needs to be updated every time there are changes made to the table.
How MySQL Optimizes WHERE Clauses?MySQL optimizes the following way:
1. Removes unnecessary parenthesis.
2. Constant expressions used by indexes are evaluated only once.
3. Early detection of invalid constant expressions.
4. HAVING is merged with WHERE if GROUPBY is not used.
5. The best Join combination is found.
6. Before each row is output, those that do not match the HAVING clause are skipped.
7. Each table index is queried and the best index is used.