Determining which columns to index in MySQL in CakePHP

Question

I have a web app, which has quite a few queries being fired from every page. As more data was added to the DB, we noticed that the pages were taking longer and longer to load.

On examining PhpMyAdmin -> Status -> Joins, we noticed this (with the number in red):

Select_full_join 348.6 k The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

1. How do I determine which joins are causing the problems? Are all the joins equally to be blamed?
2. How do I determine which columns should be indexed, for the performance to be proper?

We are using CakePHP + MySQL, and the queries are all auto-generated.

Answer 1

The rule of thumb that I have always used, is that if I am using join, the fields that I am joining on need to be indexed.

For instance, if you have a query like the following:

SELECT t1.name, t2.salary
    FROM employee AS t1 
    INNER JOIN info AS t2 ON t1.name = t2.name;

Both t1.name and t2.name should be indexed.

Below are some good reads for this as well:

Optimizing MySQL: Importance of JOIN Order

How to optimize MySQL JOIN queries through indexing

And in general, this guy's site has some good info as well.

MySQL Optimizer Team

Edit: This is always helpful.

And if you have access to your server settings, check out:

MySQL Slow Server Logs

Once you have a log of slow queries, you can use explain on them to see what needs indexing.