MySQL Performance Tips

SQL queries refactoring

It is always advisable to only get the necessary data when issuing queries to MySQL server. Each query has its cost: both the amount of data transferred from MySQL server and complexity of the query itself might affect page load times greatly.

Getting only necessary, required data

When running select queries, if you don't need to use all information from fetched rows and only specific columns, it is always better to specify only those columns instead of the commonly used asterisk. Imaging you have a table Customer and have a PHP page which displays list of customers with links to their detail pages.

<?php
foreach($customers as $customer) {
   echo '<p><a href="/customers/view/id/' . $customer->id . '">' . $customer->name . '</a></p>';
}
?> your query might look like: SELECT * FROM Customer Or in Zend Framework case: <?php
$customers = new Customers();
$select = $customers->select();
$customers = $customers->fetchAll($select);
?> You might want to replace it with SELECT id, name FROM Customer <?php
$customers = new Customers();
$select = $customers->select()->from('Customer', array('id', 'name');
$customers = $customers->fetchAll($select);
?> Another example is when you need to get count of rows collection. If count is the only information you need to get, then use MySQL COUNT function instead of running count () function on fetched rows. <?php
$customers = new Customers();
$select = $customers->select()->where('sex = ?', 'male');
$count = count($customers->fetchAll($select));
?> woulr be replaced by <?php
$customers = new Customers();
$db = $customers->getAdapter();
$select = $db->select()->from('Customer', array('count' => new Zend_Db_Expr('COUNT(id)')));
$count = $db->fetchOne($select);
?> Note that COUNT (id) will work faster than COUNT (*).

Minimize the number of queries

This is trivial. Instead of getting data from database several times, when you are confident that data is not changed, there is no need to run the same query. Instead, save returned data in memory and use it.

Some patters for turning complex queries into smart, simple and faster queries

Getting master record and aggregated data from detail table

Here is example of when you need it. Suppose you have Customer table and additionally the Sale table. Sale contains all customers' orders. Customer: id, name, etc.
Sale: id, customer_id, amount, description, etc.

You want to display a table of customers and summary amount for sales of each customer.

Bad approach (nested query): SELECT id, name, (SELECT SUM(amount) FROM Sale WHERE customer_id = c.id) AS summary FROM Customer AS c A better approach is using join: SELECT c.id, c.name, SUM(amount) AS summary
FROM Sale AS s LEFT JOIN Customer AS c
ON s.cutomer_id = c.id
GROUP BY s.customer_id

Listing duplicate values in a column

You want to get rows which have the same column values

SELECT COUNT(*) as count, name,  
FROM Customer
GROUP BY ucase(name, year_born)
HAVING count > 1
ORDER BY count DESC; will get customers (and count) who have the same name and were born on the same year.

Finally, tune up your server

There is more to optimizing MySQL usage than just queries. Depending on your website load, you might need to tweak the server settings. A very helpful tool for Linux users is mysqltuner.pl script. Login to your shell, and run "wget mysqltuner.pl" (that simple). Then "chmod +x mysqltuner.pl" and running it via "./mysqltuner.pl" will list admin friendly advices for setting correct values of MySQL configuration specific to your data and MySQL load.

Note: some of the queries are untested. Feel free to comment and correct me :)