Optimize a MySQL database

One of the important thing for achieving MySQL database performance is indexing that allows faster gathering of data to optimize MySQL database.

Here is the list to Optimize MySQL database:

• Indexing in MySQL will create an internal register which is saved in by the MySQL service.For e.g.ALTER TABLE table_name ADD INDEX (column_name);
• There is another query by which you can increase the loading speed and performance of the database also used to reclaim unused space in a MySQL.

  OPTIMIZE TABLE table_name;

  Using mysqlcheck command line you can optimize like:

  mysqlcheck -o

  You can use mysqlcheck to do this at the command line.

  One database:

  mysqlcheck -o

  All databases:

  mysqlcheck -o –all-databases

• Use EXPLAIN to determine the queries are functioning appropriately with less time.
• Avoid count(*) on tables, it can lock the entire table.
• Use GROUP BY instead of DISTINCT when necessary.
• Avoid using ORDER BY RAND().
• Avoid sub-queries, use UNION in WHERE clauses.
• Sometimes MySQL chooses the wrong index, use USE INDEX for this case.
• Use slow query log to find slow queries in a database.
• Use Triggers economically.
• Use linking tables to another rather than extending multiple rows.

After the optimization procedure, the MySQL service will take much less time for searching in your database that will result in better performance of any scripts.

Read Also: 5 Optimizing Web Performance Speed Test Tools

There are other methods that can be used for MySQL optimization.
For e.g. using of persistent connections, query caching, etc.
However, this may require some fine tuning from the Apache and MySQL configuration files.