How can I optimize this query?

Question

I have the following query:

SELECT `masters_tp`.*, `masters_cp`.`cp` as cp, `masters_cp`.`punti` as punti
FROM (`masters_tp`)
LEFT JOIN `masters_cp` ON `masters_cp`.`nickname` = `masters_tp`.`nickname`
WHERE `masters_tp`.`stake` = 'report_A'
AND `masters_cp`.`stake` = 'report_A'
ORDER BY `masters_tp`.`tp` DESC, `masters_cp`.`punti` DESC
LIMIT 400;

Is there something wrong with this query that could affect the server memory?

Here is the output of EXPLAIN

| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                                        |
+----+-------------+------------+------+---------------+------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | masters_cp | ALL  | NULL          | NULL |    NULL | NULL |  8943 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | masters_tp | ALL  | NULL          | NULL |    NULL | NULL | 12693 | Using where                                  |

Answer 1

It is simple query. I think everything is ok with it. You can try add indexes on 'stake' fields or make limit lower.

Answer 2

Run the same query prefixed with EXPLAIN and add the output to your question - this will show what indexes you are using and the number of rows being analyzed.

You can see from your explain that no indexes are being used, and its having to look at thousands of rows to get your result. Try adding an index on the columns used to perform the join, e.g. nickname and stake:

ALTER TABLE masters_tp ADD INDEX(nickname),ADD INDEX(stake);
ALTER TABLE masters_cp ADD INDEX(nickname),ADD INDEX(stake);

(I've assumed the columns might have duplicated values, if not, use UNIQUE rather than INDEX). See the MySQL manual for more information.

Answer 3

Replace the "masters_tp.* " bit by explicitly naming only the fields from that table you actually need. Even if you need them all, name them all.

Answer 4

There's actually no reason to do a left join here. You're using your filters to whisk away any leftiness of the join. Try this:

SELECT 
    `masters_tp`.*, 
    `masters_cp`.`cp` as cp, 
    `masters_cp`.`punti` as punti
FROM 
    `masters_tp`
    INNER JOIN `masters_cp` ON 
        `masters_tp`.`stake` = `masters_cp`.stake`
        and `masters_tp`.`nickname` = `masters_cp`.`nickname`
WHERE 
    `masters_tp`.`stake` = 'report_A'
ORDER BY 
    `masters_tp`.`tp` DESC, 
    `masters_cp`.`punti` DESC
LIMIT 400;

inner joins tend to be faster than left joins. The query can limit the number of rows that have to be joined using the predicates (aka the where clause). This means that the database is handling, potentially, a lot less rows, which obviously speeds things up.

Additionally, make sure you have a non-clustered index on stake and nickname (in that order).