[Solved] MySQL optimizing view

EverSQL Database Performance Knowledge Base

MySQL optimizing view

Database type:

I'm working on an ASP.NET application that use the following query to retrieve data to feed into a GridView. The query works seems to work fine but it's very very slow to complete (even if run trough MySQL Workbench). With a pretty poor database (i.e. not with as much data as it supposed to be populated) the query take around 7 seconds to complete. The query is defined as a View in MySQL and it's bound to the GridView trough an SQLDataSource. I would like to optimize it but I'm not that good in SQL.

SELECT 
        `noleggio`.`iddocumento` AS `iddocumento`,
        `noleggio`.`numero` AS `numero`,
        `noleggio`.`serie` AS `serie`,
        `noleggio`.`data` AS `data`,
        `noleggio`.`anno` AS `anno`,
        `noleggio`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
        `noleggio`.`imponibile` AS `imponibile`,
        `noleggio`.`totale` AS `totale`,
        `noleggio`.`tipo_documento` AS `tipo_documento`,
        `noleggio`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
        `noleggio`.`data_pagamento` AS `data_pagamento`,
        `noleggio`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
        `noleggio`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
        `noleggio`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
        `noleggio`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile`,
        `noleggio`.`id_noleggio` AS `id_noleggio`,
        `stati`.`data_stato` AS `data_stato`,
        `stati`.`stato` AS `stato_sdi`,
        `noleggio_veicoli_fatturazione`.`data_incasso_saldo` AS `data_incasso_saldo`
    FROM
        (((SELECT 
            `noleggio`.`iddocumento` AS `iddocumento`,
                `noleggio`.`numero` AS `numero`,
                `noleggio`.`serie` AS `serie`,
                `noleggio`.`data` AS `data`,
                `noleggio`.`anno` AS `anno`,
                `noleggio`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
                `noleggio`.`imponibile` AS `imponibile`,
                `noleggio`.`totale` AS `totale`,
                `noleggio`.`tipo_documento` AS `tipo_documento`,
                `noleggio`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
                `noleggio`.`data_pagamento` AS `data_pagamento`,
                `noleggio`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
                `noleggio`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
                `noleggio`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
                `noleggio`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile`,
                `doc_righe`.`id_noleggio` AS `id_noleggio`
        FROM
            (`doc_righe`
        JOIN (SELECT 
            `doc_testa`.`id` AS `iddocumento`,
                `doc_testa`.`numero` AS `numero`,
                `doc_testa`.`serie` AS `serie`,
                `doc_testa`.`data` AS `data`,
                `doc_testa`.`anno` AS `anno`,
                `doc_testa`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
                `doc_testa`.`imponibile` AS `imponibile`,
                `doc_testa`.`totale` AS `totale`,
                `doc_testa`.`tipo_documento` AS `tipo_documento`,
                `doc_testa`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
                `doc_testa`.`data_pagamento` AS `data_pagamento`,
                `doc_testa`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
                `doc_testa`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
                `doc_testa`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
                `doc_testa`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile`
        FROM
            `doc_testa`) `noleggio` ON ((`noleggio`.`iddocumento` = `doc_righe`.`id_testa`)))
        GROUP BY `noleggio`.`iddocumento`) `noleggio`
        LEFT JOIN `noleggio_veicoli_fatturazione` ON ((`noleggio`.`id_noleggio` = `noleggio_veicoli_fatturazione`.`id_noleggio`)))
        LEFT JOIN (SELECT 
            `transizioni_sdi`.`id` AS `id`,
                `transizioni_sdi`.`id_doc` AS `id_doc`,
                MAX(`transizioni_sdi`.`data_stato`) AS `data_stato`,
                `tab_stati_sdi`.`stato` AS `stato`
        FROM
            (`transizioni_sdi`
        JOIN `tab_stati_sdi` ON ((`tab_stati_sdi`.`id` = `transizioni_sdi`.`stato`)))
        GROUP BY `transizioni_sdi`.`id_doc`) `stati` ON ((`noleggio`.`iddocumento` = `stati`.`id_doc`)))
    ORDER BY `noleggio`.`numero`;

Please note that in original that query was using RIGHT JOIN statements but when I saved it as a View into MySQl WB the editor for some reason solved RIGHT JOINS with this structure. This was the original query:

SELECT 
    noleggio.*,
    stati.data_stato AS data_stato,
    stati.stato AS stato_sdi,
    data_incasso_saldo
FROM
    noleggio_veicoli_fatturazione
        RIGHT JOIN
    (SELECT 
        noleggio.*, id_noleggio
    FROM
        doc_righe
    JOIN (SELECT 
        id AS iddocumento,
            anno,
            numero,
            serie,
            data,
            cliente_ragione_sociale,
            imponibile,
            totale,
            tipo_documento,
            tipo_documento_dettaglio,
            data_pagamento,
            aliquota_ritenuta_acconto,
            ra_percentuale_importo_imponibile,
            aliquota_ritenuta_enasarco,
            re_percentuale_importo_imponibile
    FROM
        doc_testa) AS noleggio ON noleggio.iddocumento = doc_righe.id_testa
    GROUP BY iddocumento) AS noleggio ON noleggio.id_noleggio = noleggio_veicoli_fatturazione.id_noleggio
        LEFT JOIN
    (SELECT 
        transizioni_sdi.id,
            id_doc,
            MAX(data_stato) AS data_stato,
            tab_stati_sdi.stato AS stato
    FROM
        transizioni_sdi
    INNER JOIN tab_stati_sdi ON tab_stati_sdi.id = transizioni_sdi.stato
    GROUP BY id_doc) AS stati ON noleggio.iddocumento = stati.id_doc
ORDER BY numero DESC;

The query was meant to find all doc_testa records with at least one matching record in doc_righe (always true in this application) the only needed field from doc_righe is id_noleggio (of course such field value could be null). id_noleggio is used to find match in noleggio_veicoli_fatturazione (if any) and retrieve data_incasso_saldo value. For all doc_testa record we need then to find the last matching tab_stati_sdi.stato value (if any).

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Correlated Subqueries (query line: 22): A correlated subquery is a subquery that contains a reference (column: iddocumento) to a table that also appears in the outer query. Usually correlated queries can be rewritten with a join clause, which is the best practice. The database optimizer handles joins much better than correlated subqueries. Therefore, rephrasing the query with a join will allow the optimizer to use the most efficient execution plan for the query.
  2. Avoid Subqueries (query line: 43): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  3. Avoid Subqueries (query line: 81): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  5. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `tab_stati_sdi` ADD INDEX `tab_sdi_idx_id` (`id`);
ALTER TABLE `transizioni_sdi` ADD INDEX `transizioni_sdi_idx_id_doc` (`id_doc`);
The optimized query:
SELECT
        `noleggio`.`iddocumento` AS `iddocumento`,
        `noleggio`.`numero` AS `numero`,
        `noleggio`.`serie` AS `serie`,
        `noleggio`.`data` AS `data`,
        `noleggio`.`anno` AS `anno`,
        `noleggio`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
        `noleggio`.`imponibile` AS `imponibile`,
        `noleggio`.`totale` AS `totale`,
        `noleggio`.`tipo_documento` AS `tipo_documento`,
        `noleggio`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
        `noleggio`.`data_pagamento` AS `data_pagamento`,
        `noleggio`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
        `noleggio`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
        `noleggio`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
        `noleggio`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile`,
        `noleggio`.`id_noleggio` AS `id_noleggio`,
        `stati`.`data_stato` AS `data_stato`,
        `stati`.`stato` AS `stato_sdi`,
        `noleggio_veicoli_fatturazione`.`data_incasso_saldo` AS `data_incasso_saldo` 
    FROM
        (((SELECT
            `noleggio`.`iddocumento` AS `iddocumento`,
            `noleggio`.`numero` AS `numero`,
            `noleggio`.`serie` AS `serie`,
            `noleggio`.`data` AS `data`,
            `noleggio`.`anno` AS `anno`,
            `noleggio`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
            `noleggio`.`imponibile` AS `imponibile`,
            `noleggio`.`totale` AS `totale`,
            `noleggio`.`tipo_documento` AS `tipo_documento`,
            `noleggio`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
            `noleggio`.`data_pagamento` AS `data_pagamento`,
            `noleggio`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
            `noleggio`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
            `noleggio`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
            `noleggio`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile`,
            `doc_righe`.`id_noleggio` AS `id_noleggio` 
        FROM
            (`doc_righe` 
        JOIN
            (
                SELECT
                    `doc_testa`.`id` AS `iddocumento`,
                    `doc_testa`.`numero` AS `numero`,
                    `doc_testa`.`serie` AS `serie`,
                    `doc_testa`.`data` AS `data`,
                    `doc_testa`.`anno` AS `anno`,
                    `doc_testa`.`cliente_ragione_sociale` AS `cliente_ragione_sociale`,
                    `doc_testa`.`imponibile` AS `imponibile`,
                    `doc_testa`.`totale` AS `totale`,
                    `doc_testa`.`tipo_documento` AS `tipo_documento`,
                    `doc_testa`.`tipo_documento_dettaglio` AS `tipo_documento_dettaglio`,
                    `doc_testa`.`data_pagamento` AS `data_pagamento`,
                    `doc_testa`.`aliquota_ritenuta_acconto` AS `aliquota_ritenuta_acconto`,
                    `doc_testa`.`ra_percentuale_importo_imponibile` AS `ra_percentuale_importo_imponibile`,
                    `doc_testa`.`aliquota_ritenuta_enasarco` AS `aliquota_ritenuta_enasarco`,
                    `doc_testa`.`re_percentuale_importo_imponibile` AS `re_percentuale_importo_imponibile` 
                FROM
                    `doc_testa`
            ) `noleggio` 
                ON (
                    (
                        `noleggio`.`iddocumento` = `doc_righe`.`id_testa`
                    )
                )
            ) 
    GROUP BY
        `noleggio`.`iddocumento` 
    ORDER BY
        NULL) `noleggio` 
    LEFT JOIN
        `noleggio_veicoli_fatturazione` 
            ON (
                (
                    `noleggio`.`id_noleggio` = `noleggio_veicoli_fatturazione`.`id_noleggio`
                )
            )) 
LEFT JOIN
    (
        SELECT
            `transizioni_sdi`.`id` AS `id`,
            `transizioni_sdi`.`id_doc` AS `id_doc`,
            MAX(`transizioni_sdi`.`data_stato`) AS `data_stato`,
            `tab_stati_sdi`.`stato` AS `stato` 
        FROM
            (`transizioni_sdi` 
        JOIN
            `tab_stati_sdi` 
                ON (
                    (
                        `tab_stati_sdi`.`id` = `transizioni_sdi`.`stato`
                    )
                )
            ) 
    GROUP BY
        `transizioni_sdi`.`id_doc` 
    ORDER BY
        NULL
) `stati` 
    ON (
        (
            `noleggio`.`iddocumento` = `stati`.`id_doc`
        )
    )
) 
ORDER BY
`noleggio`.`numero`

Related Articles



* original question posted on StackOverflow here.