Google Cloud SQL slow for queries Using filesort

Question

mysql> EXPLAIN SELECT * FROM `condominio_boleto` 
INNER JOIN `contrato_contrato` ON (`condominio_boleto`.`contrato_id` = `contrato_contrato`.`id`) 
INNER JOIN `cadastro_imovel` ON (`contrato_contrato`.`imovel_id` = `cadastro_imovel`.`id`) 
INNER JOIN `cadastro_pessoa` ON (`contrato_contrato`.`pessoa_id` = `cadastro_pessoa`.`id`) 
ORDER BY `condominio_boleto`.`id` DESC LIMIT 1;

+----+-------------+-------------------+--------+---------------------------------------------------------------+----------------------------+---------+------------------------------------+------+---------------------------------+
| id | select_type | table             | type   | possible_keys                                                 | key                        | key_len | ref                                | rows | Extra                           |
+----+-------------+-------------------+--------+---------------------------------------------------------------+----------------------------+---------+------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | cadastro_imovel   | ALL    | PRIMARY                                                       | NULL                       | NULL    | NULL                               |  128 | Using temporary; Using filesort |
|  1 | SIMPLE      | contrato_contrato | ref    | PRIMARY,contrato_contrato_33999a20,contrato_contrato_8b5ebd9d | contrato_contrato_33999a20 | 4       | mydb.cadastro_imovel.id            |    1 |                                 |
|  1 | SIMPLE      | cadastro_pessoa   | eq_ref | PRIMARY                                                       | PRIMARY                    | 4       | mydb.contrato_contrato.pessoa_id   |    1 |                                 |
|  1 | SIMPLE      | condominio_boleto | ref    | condominio_boleto_91c8cd68                                    | condominio_boleto_91c8cd68 | 4       | mydb.contrato_contrato.id          |    9 |                                 |
+----+-------------+-------------------+--------+---------------------------------------------------------------+----------------------------+---------+------------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)

This query is taking 3-4 seconds to run on Google Cloud SQL (D0 instance). If I remove the ORDER BY clause it no longer shows the Extra Using temporary; Using filesort and speeds up to <100ms. But because it's auto-genreated by Django admin I can't remove that ORDER BY clause.

All these tables are really small. condominio_boleto has 5k records all other tables have less than 500 records.

Can I speed this up with indexes? Is this a known problem on Google Cloud SQL?

Operations that use the temporary files are slow in Cloud SQL. Could you please write to [email protected] with the exact tables? That will allow us to tell you if something else is causing the slowness or if adding indexes can help. — Razvan Musaloiu-E.

Syclone Syclone · Accepted Answer · 2014-08-07T18:47:05

I had a similar experience on the Google Cloud SQL Tier D0 (128MB RAM). One of my website was running very slow and took a long time to return pages. After running Jet Profiler I found that my database queries were running slow (2-3s to execute and 7 threads on average). The problem queries were those with inner joins and orders. So I upgraded to Tier D1 (512MB RAM) and as expected, no more slow queries. My guess is D0 isn't made to handle height load or complex queries. It's mostly suited for low usage and testing.

Google Cloud SQL slow for queries Using filesort

1 Answers