First of all, fixing slow MySQL queries requires finding out which queries are causing problems by using the slow query log. Then you can check them with EXPLAIN to understand how the tables and indexes are being accessed. By placing the right indexes on the columns that are the most used in the WHERE, JOIN, and ORDER BY clauses, you will drastically reduce the time of scanning. You should avoid SELECT * at all costs and only retrieve the columns that you really need. Also, you can do things like improving joins, getting rid of unnecessary subqueries, and using pagination to control large result sets. Besides, it is very important that your tables are normalized and that the data types are suited to the content. Furthermore, activating query caching, besides increasing the buffer sizes, and performing regular table optimizations are also great trends. Keeping a check on performance would be very beneficial for nipping the issues in the bud and helping in the smooth running of queries.
