Difference between local MySQL query and production server query execution times

Question

I am building a site that uses a lot of database queries, so I was kind of afraid this might happen.

So, the issue here is this, I have several queries that use a lot of JOINs and some of the tables have a couple of thousand entries while some of the others have about 200-300 thousand entries. I had experience of a site slowing and I had to optimize some queries.

The thing is that in this case on my local computer the specific section that uses those queries takes about 2.5 seconds to load with network throttling enabled as Regular wi-fi. With Good Wi-Fi it takes about 1.3 seconds to load.

On my production server which is a virtual machine on DigitalOcean it takes about 5 minutes! to load the exact same content with the exact same query. Now I am no expert, but my computer is not 120 times faster than the production server on DigitalOcean.

My laptop has the following specs: Intel Core i7-6700 HQ, 16 GB of DDR4 RAM and the server is running on a 5400 RPM HDD, it's not even on my SSD drive, that's only where the MySQL engine is.

The production server was originally a basic DO instance with 1GB of RAM and 1 VCPU. I thought that it probably needed some boosting so I temporarily upgraded it to have 2VCPUs and 2 GB of RAM, but it made no difference. The other sections load blazingly fast, except for the one that uses a lot of joins.

Now, I'm no expert, but my computer is not 120 times faster than the server and it also runs a bunch of other processes. I do have a GeForce 1070M in it, but I don't think that affects mysql performance.

I tried separating the query in as little JOINs as possible and then execute multiple simple queries to add the additional information to my information array, but then I got a different problem. With that logic even on my computer it got stuck for about 4-5 seconds and then it suddenly loaded the content.

There are screenshots of Chrome's network tab below that show the timing difference. As you can see everything else loads blazingly fast except for the initial load. I am pretty sure it's a MySQL issue, but the difference is staggering. I am thinking of trying to load the site on a 16GB of memory instance with 6VCPUs on DigitalOcean to see if it's memory/cpu related, but I am not sure my client would like to pay 80 USD a month or more for that kind of VM.

One possible solution I was thinking of was to divide the Localidades and Asentamientos tables (they both have about 200-300k entries) into 32 smaller tables, one for each state of Mexico and have a special function for each state to reference the other table, but I don't think that would be neither scalable nor good practice.

I also added a calculated cost of the query below.

My local computer has:

• Windows 10 1803
• Apache/2.4.25 (Win64)
• MySQL 5.7.23

My production server has:

• Ubuntu 18.04.1 LTS
• Apache/2.4.29 (Ubuntu)
• 5.7.24-0ubuntu0.18.04.1

Any idea what I can do to solve this?

The generated query is as follows:

SELECT 
    `Propiedades`.*,
    `Propiedades`.`directorio` AS `main_dir`,
    DATEDIFF(Propiedades.fecha_finalizacion,
            '2018-12-02 11:11:49') AS quedan,
    `OperacionesPorPropiedad`.*,
    `Operaciones`.`nombre_operacion`,
    `Operaciones`.`nombre_operacion_slug`,
    `TiposDePropiedades`.*,
    `FotografiasPorPropiedad`.*,
    `Empresas`.`nombre_empresa`,
    `Estados`.*,
    `Municipios`.*,
    `Localidades`.*,
    `Asentamientos`.*,
    `Clientes`.`nombres`,
    `Clientes`.`apellidos`,
    `Clientes`.`email`,
    `TiposDeClientes`.*
FROM
    `Propiedades`
        JOIN
    `OperacionesPorPropiedad` ON `OperacionesPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        JOIN
    `Operaciones` ON (`Operaciones`.`id_operacion` = `OperacionesPorPropiedad`.`id_operacion`
        AND `OperacionesPorPropiedad`.`id_propiedad` = Propiedades.id_propiedad)
        JOIN
    `TiposDePropiedades` ON `TiposDePropiedades`.`id_tipo` = `Propiedades`.`id_tipo`
        JOIN
    `FotografiasPorPropiedad` ON (`FotografiasPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        AND `FotografiasPorPropiedad`.`orden` = 1)
        JOIN
    `Empresas` ON `Empresas`.`id_empresa` = `Propiedades`.`id_empresa`
        JOIN
    `Estados` ON `Estados`.`id_estado` = `Propiedades`.`id_estado`
        LEFT OUTER JOIN
    `Municipios` ON `Municipios`.`id_municipio` = `Propiedades`.`id_municipio`
        LEFT OUTER JOIN
    `Localidades` ON `Localidades`.`id_localidad` = `Propiedades`.`id_localidad`
        LEFT OUTER JOIN
    `Asentamientos` ON `Asentamientos`.`id_asentamiento` = `Propiedades`.`id_asentamiento`
        JOIN
    `Clientes` ON `Clientes`.`id_cliente` = `Empresas`.`id_cliente`
        JOIN
    `TiposDeClientes` ON (`Clientes`.`id_tipo_cliente` = `TiposDeClientes`.`id_tipo_cliente`
        AND `Clientes`.`id_cliente` = `Empresas`.`id_cliente`)
WHERE
    `Propiedades`.`id_estatus_propiedad` = 1
GROUP BY `Propiedades`.`id_propiedad`
ORDER BY FIELD(`Propiedades`.`destacada`, '1', '0') , FIELD(`Clientes`.`id_tipo_cliente`, 1, 2, 3) , RAND()
LIMIT 24

Answer 1

Would this give you a reasonable 24 rows? Or do you depend on filtering from the other tables?

    WHERE  P.`id_estatus_propiedad` = 1
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) ,
              RAND()
    LIMIT  24

If so, then consider the following:

Your current query is hauling around full rows from lots of tables, then shuffling them, and finally delivering only 24.

A better way is to figure which 24, then go for the details:

SELECT lots-of-stuff
    FROM ( SELECT id_propiedad
               FROM Propiedades AS P1
               JOIN ...   -- as few as needed to get to Clientes
               JOIN  `Clientes` AS C1  ON C1.`id_cliente` = Em.`id_cliente`
               WHERE  P1.`id_estatus_propiedad` = 1
               ORDER BY  FIELD(P1.`destacada`, '1', '0') ,
                         FIELD(C1.`id_tipo_cliente`, 1, 2, 3) ,
                         RAND()
               LIMIT  24
         ) AS x
    JOIN  `Propiedades` AS P  ON P.id_propiedad = x.id_propiedad
    JOIN  `OperacionesPorPropiedad` AS OP  ON OP.`id_propiedad` = P.`id_propiedad`
    JOIN  `Operaciones` AS O  ON (O.`id_operacion` = OP.`id_operacion` ...
    ...
    -- no WHERE, GROUP BY, or LIMIT, but repeat the ORDER BY:
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) , RAND()

Back to the question of performance difference...

• Your personal machine has a bigger value for innodb_buffer_pool_size than the tiny VM in the cloud?
• You are fetching all the columns from many of the rows of about a dozen table.
• You are (currently) collecting the mass of potential output rows first, then using GROUP BY to eliminated dups and finally LIMITing to just 24. The size of the temp table is probably huge. ("inflate-deflate" syndrome of JOIN plus GROUP BY.
• You possibly have TEXT columns in some of those * column lists; this aggravates the temp table issue.

Those combine to cause fast/slow performance. My suggestion, if viable, eliminates most of it.

Also FotografiasPorPropiedad needs INDEX(id_propiedad, orden) (in either order).

Answer 2

Sorry for taking your time guys... It was a rookie mistake in which I didn't read the error messages when importing the database.

When I generated the mysqldump, some table names were incorrectly generated with lowercase-only letters and that caused an error when importing.

Since the indexes of everything were after the erroneous instructions they never got executed so I basically did non-indexed full table scans and that's why it took like forever to load results.

I corrected my SQL file and created the database again and it worked like a charm. Sorry for wasting your time guys.

PS: I actually boosted the server to 16GB of RAM and 6VCPUs and it made no difference whatsoever.