• Optimize MySQL Database Performance Using Indexes

    Date: 2011.03.17 | Category: Technology | Tags:

    Recently we found a client’s script causing high MySQL CPU usage. MySQL was using almost 200% CPU when his script run.

    There were few concurrent queries. We noticed his table has more than 2 million records and all queries included regular expressions. By checking the structure of the table, we found no indexes were used on columns that are frequently used in where clauses. So we created an index on the most frequently used column to see if that help. MySQL’s CPU usage fell to 10% immediately.

    Similar issues may exist on your sites too. If you find that your SQL query is slow or the site bites too many system resources, you may need to check the indexes of your database.

    Indexes are best used on columns that are frequently used in where clauses, and in any kind of sorting, such as "order by". It can significantly improve sql query performance.