MySQL Server Performance Tips

MySQL Server Performance Tips:

  • Do regular maintenance of MyISAM tables with analyze and optimize commands.
  • Choose better backup policy for DBs. It completely depends on which storage engine we are using.
  • Always try compress static data so can save the space and no need to take backup often.
  • Check MySQL memory utilization before set any high value for buffer parameters.
  • While using InnoDB storage engine,always use INNODB_FILE_PER_TABLE.
  • Use benchmarking to identify current performance of server.
  • Keep in mind that regular backup should not affect the server performance during peak time.
  • Always design schema properly, so when data will grow and if we want to change the structure or something it should be easy to scale.

DB Design Tips:

  • Keep in mind that 3rd normal form is not always good. Design database as per requirement and try to use less joins.
  • Always try to use boolean flags rather than create any varchar field.
  • Indexes are always good for performance but dont create index on every field.
  • Avoid to create duplicate indexes as it will not being used and consume more space.
  • For the email address column in large tables, try to create reverse column and than make index on it. It will be faster.
  • Use appropriate data types always. Unnecessary, avoid to give large value to data types.
  • Try to avoid NULL. Rather than that give ‘ ‘ for text field and 0 for numeric field.

MySQL Query Optimization Tips

  • Use EXPLAIN to profile the query execution plan
  • Use Slow Query Log to track down slow queries
  • Don’t use DISTINCT when you have or could use GROUP BY
  • Use LOAD DATA instead of INSERT while inserting bulk records.
  • Don’t use ORDER BY RAND() if you have less records. Its for large amount of data
  • Use SQL_NO_CACHE when you are SELECT frequently updated data or large sets of data
  • Always avoid wildcards at the start of LIKE queries. It will not use any index.
  • Always keep in mind that complex queries should be split.
  • Try to use caching for similar queries.
  • Don’t use COUNT (*) on any Innodb tables for search,If you want total no of rows, than use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() functions.

MySQL Configuration Parameters Tips

  • For InnoDB try to use innodb_flush_method (O_DIRECT). It will bypass OS filecache for innodb data files.
  • Before set any parameter, check that its session level or global level parameter and set value according.
  • Try to use session level parameters while doing any bulk operations.
  • Run MySQL with STRICT SQL_MODE so server can give proper warnings.
  • Maximum time, try to use –skip-name-resolve for connect mysql server through hostname.

3 thoughts on “MySQL Server Performance Tips

  1. Hi Neel,

    This blog is really helpful. Keep up the good work.

    I have one confusion. What will be the BEST optimum solution to use in select or update statement:

    1. A JOIN B ON A.email = B.email
    2. A LEFT JOIN B ON A.email = B.email WHERE B.email IS NOT NULL

    - Rajesh

Leave a Reply