How to fix slow SQL queries.

Step #1: How To Catch A Slow Query.

First, you need to know which are the queries that are running slow in the first place.

The easiest way to do that is to use the PHP hrtime() function.

This function works as a precise timer that lets you calculate the time elapsed between two lines of code.
More precisely, hrtime() gives you the current timestamp in nanoseconds (1 billion nanoseconds = 1 second).

Here's how it works:

$time1 = hrtime(true);
sleep(3);
$time2 = hrtime(true);
$elapsed = ($time2 - $time1) / 1e+9;
echo "Time elapsed: " . round($elapsed) . " seconds";

The above example prints "Time elapsed: 3 seconds", because the sleep(3) statement takes exactly that time.
Note how you need to divide the time difference by 1 billion (1e+9) to convert nanoseconds into seconds.

You can use the same technique to see how long an SQL query takes to run.
You must take the first timestamp just before executing the query, and the second one after the execution.
Like this:

$query = "SELECT * FROM items WHERE item_id = 1";
$time1 = hrtime(true);
$result = mysqli_query($mysqli, $query);
$time2 = hrtime(true);
$elapsed = ($time2 - $time1) / 1e+6;
echo "Time elapsed: " . round($elapsed) . " milliseconds";

In this case you want to see the result in milliseconds, so you divide the result by 1 million (1e+6).

How much time is too much for an SQL query?
There's no exact answer, but you can follow these rules of thumb:

Simple queries that return one or few rows should run in less than 50 milliseconds.
Queries with JOINs, subqueries or complex search patterns are usually slower, but they should stay below 100-200 milliseconds.
Very complex queries, like the ones updating a lot of rows, can be even slower but you should avoid going above 500 milliseconds.

Step #2: How To Analyze A Slow Query.

Once you have selected the queries that need some optimization, it's time to analyze them.
The first step is to echo your query:

$id = mysqli_real_escape_string($mysql, $itemId);

$query = 'SELECT * FROM items, prices WHERE item_id = ' . $id . ' AND price_item_id = item_id';

echo $query;

This way you can analyze the query exactly as your PHP script executes it.
(If you need more examples of how to use PHP and MySQL, here's the tutorial for you.)

A good starting point to analyze your query is to use the SQL EXPLAIN command.
This command tells you how the SQL engine executes the query, including which indexes are used, if full-table searches are performed, and so on.

For example, let's say that your query is this:
SELECT * FROM items, prices WHERE item_id = 5 AND price_item_id = item_id

Open an SQL terminal or the phpMyAdmin SQL editor.
Paste the above query and add "EXPLAIN" before it:

EXPLAIN SELECT * FROM items, prices WHERE item_id = 5 AND price_item_id = item_id;

Now run the query and look at the result.
(NOTE: you want to run a valid query that makes sense for your own database, otherwise you will get an error.)

The result from EXPLAIN is a table with some information about the query execution.
The first two fields to look at are key and rows.

The key field tells which index key is used, if any.
Searches using an index are much faster than searches that simply scan all the table.
So, if you see that this field is set to NULL, you should consider changing the query so that it uses an index or set a new index on the table (more on this later).

The rows field tells how many rows are considered for the search operation.
If the query does not use an index, here you will find the total number of rows in the table.
The more specific the index is, the lower this number will be.
A low rows number means that the search operation will be fast. Otherwise, it means that either you are not using an index, or your index is not very effective because too many rows share the same index value.

Step #3: Optimize Your Query.

So, you have identified and analyzed your slow query.
Now what?

The simplest way to make your query faster is to optimize it.
Query optimization is a vast topic, but you can start from these simple steps:

Use indexes.
If your query has one or more WHERE conditions, make sure you leverage the table's indexes. Using indexes results in dramatically faster search operations.
Avoid subqueries.
Subqueries are handy, and sometimes it makes perfect sense to use them. However, subqueries are often slower than we think because of how SQL works.
In many cases, you can fix a slow subquery by executing it separately from the main query beforehand, and then use the result values in an IN() statement in the main query.
Avoid large JOINs.
Similarly to subqueries, JOINs can become very slow when large tables are selected. Only use JOINs when really necessary.

Step #4: Optimize Your Database.

The last step is to optimize your database.
Just like you can make your queries leverage your database structure, you can also tweak your database structure to meet your queries requirements.

So, this step is somehow the complementary of step #3.

Database optimization is a very vast topic, but here are some simple yet effective tips to get started.

Add or optimize indexes.
As you may have guessed, indexes are the first thing to look at.
If you need to search over a specific column, it may make sense to add a table index to that column. You can also consider multi-columns indexes.
Just make sure not to overdo: too many indexes can have a detrimental effect on performance.
Use unique indexes when possible.
If possible, define indexes as unique. This means that, for each unique column or multi-columns index, there can only be one row on the table.
Unique indexes are very fast, and they also help enforce the table's consistency by preventing duplicate rows.
Avoid large indexes.
Indexes are less useful when too many rows on the table share the same index value.
For example, let's say your items table has 1000 rows and you have an index on the item_type column. This index is not very effective if 500 rows have type 1 and the others have type 2. But it is much more effective if the rows are divided into 20 different types.
Use partitioning.
Tables become slower as they get larger. If you have very large tables, consider partitioning them.
Partitioning means splitting a table depending on the value of a column.
For example, let's say that you have a bills table. You can partition that table by using the date column, so that bills from different years end up in different tables.
Partitioning can be challenging but it's worth looking into it if you have very large tables.

There are much more possible optimizations you can do, but these are the ones you should start from.