MySQL performance tips

By | August 3, 2012

Tips to increase the performance of your MySQL queries.

  • Use EXPLAIN on your queries
    EXPLAIN YOUR QUERY will show details of how mysql will execute your query. This is the first thing you should use if you got slow queries.
  • Use the “Slow Query Log” to find the queries you have to optimize. If you don’t know which of your quieres are slowing down a system the slow query log helps you to find the problematic queries. Its always a good idea to have this one activated.
    <a href=”http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html”>Slow query log manual</a>
  • If you have more then 1-2 thousand records, you should avoid ORDER BY RAND at all costs. Its slowing down the query to much.
  • Its better to use group by then DISTINCT

  • Wilcards in the start of a LIKE query on indexed fields are slow. Avoid LIKE “%word” if you are you are using an index
  • If you have text or blob columns in a table but you don’t need in the result of a query, remove them from the SELECT.
  • LIMIT m,n may not be as fast as it sounds. Learn how to improve it and read more about Efficient Pagination Using MySQL
  • If you have to insert much data use LOAD DATA, it is faster then many inserts.
  • Use INSERT … ON DUPLICATE KEY update (INSERT IGNORE) instead of running a select first.

[amazon_carousel widget_type="SearchAndAdd" width="600" height="200" title="Books" market_place="" shuffle_products="False" show_border="False" keywords="MySQL" browse_node="" search_index="Books" /]

2 thoughts on “MySQL performance tips

  1. Rob Smith

    Wilcards in the start of a LIKE query are slow. Avoid LIKE “%word” <- This only applies to a field that is indexed. It has no difference when doing a table scan.

    Reply

Leave a Reply