mysqlquery-optimizationquery-performancemysql-slow-query-log

Difference between local MySQL query and production server query execution times

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:

My production server has:

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

Query cost

This is my local benchmark with throttling enabled and cache disabled

This is my remote benchmark with cache disabled

Solution

  • 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.