1

I am currently involved in maintaining a web application that requires me to display a list of documents sourced from two tables. The primary objective is to present the list and enable the user to download it in an Excel format. I have created a view to facilitate this task, but it takes approximately 20 seconds to retrieve the data, which is not acceptable.

My responsibility is to optimize the performance of the application by reducing the time taken to display the data. However, I am uncertain about where to begin with the optimization process. I have started by checking the indexes to see if they are appropriately configured but did not find any issues..

SELECT
    `oapp_forms`.`id` AS `oapp_form_id`,
    NULL AS `job_doc_id`,
    `properties`.`address1` AS `address1`,
    `properties`.`uprn` AS `uprn`,
    `properties`.`postcode` AS `postcode`,
    `oapp_forms`.`issued_date` AS `issued_date`,
    `document_types`.`document_type_name` AS `form_name`,
    `forms`.`form_code` AS `form_code`,
    `jobs`.`job_number` AS `job_number`,
    `oapp_forms`.`job_id` AS `job_id`,
    `contracts`.`client_document_name` AS `client_document_name`,
    `jobs`.`order1` AS `order1`,
    'FORM' AS `type`,
    `oapp_forms`.`updated_at` AS `updated_at`,
    `jobs`.`contract_id` AS `contract_id`,
    `jobs`.`department` AS `department`,
    `document_types`.`id` AS `document_type_id`,
    NULL AS `doc_name`,
    NULL AS `doc_number`,
    `oapp_forms`.`form_id` AS `form_id`
FROM
    (
        (
            (
                (
                    (
                        `oapp_forms`
                    LEFT JOIN `forms` ON
                        (
                            `oapp_forms`.`form_id` = `forms`.`id`
                        )
                    )
                LEFT JOIN `jobs` ON
                    (
                        `oapp_forms`.`job_id` = `jobs`.`id`
                    )
                )
            LEFT JOIN `contracts` ON
                (
                    `jobs`.`contract_id` = `contracts`.`id`
                )
            )
        LEFT JOIN `properties` ON
            (
                `jobs`.`property_id` = `properties`.`id`
            )
        )
    LEFT JOIN `document_types` ON
        (
            `forms`.`document_type_id` = `document_types`.`id`
        )
    )
WHERE
    `forms`.`is_form` = 'Yes' AND `oapp_forms`.`form_status` IN('Verified', 'Auto Verified') AND `document_types`.`is_visible_on_portal` = 'Yes' AND `oapp_forms`.`deleted_at` IS NULL AND `forms`.`deleted_at` IS NULL AND `jobs`.`deleted_at` IS NULL AND `properties`.`deleted_at` IS NULL AND `document_types`.`deleted_at` IS NULL
UNION ALL
SELECT NULL AS
    `oapp_form_id`,
    `job_docs`.`id` AS `job_doc_id`,
    `properties`.`uprn` AS `uprn`,
    `properties`.`address1` AS `address1`,
    `properties`.`postcode` AS `postcode`,
    `job_docs`.`doc_issue_date` AS `issued_date`,
    `document_types`.`document_type_name` AS `form_name`,
    `document_types`.`document_type_name` AS `form_code`,
    `jobs`.`job_number` AS `job_number`,
    `job_docs`.`job_id` AS `job_id`,
    `contracts`.`client_document_name` AS `client_document_name`,
    `jobs`.`order1` AS `order1`,
    'DOC' AS `type`,
    `job_docs`.`created_at` AS `updated_at`,
    `jobs`.`contract_id` AS `contract_id`,
    `jobs`.`department` AS `department`,
    `job_docs`.`doc_type_id` AS `document_type_id`,
    `job_docs`.`doc_name` AS `doc_name`,
    `job_docs`.`doc_number` AS `doc_number`,
    NULL AS `form_id`
FROM
    (
        (
            (
                (
                    `job_docs`
                LEFT JOIN `document_types` ON
                    (
                        `job_docs`.`doc_type_id` = `document_types`.`id`
                    )
                )
            LEFT JOIN `jobs` ON
                (`job_docs`.`job_id` = `jobs`.`id`)
            )
        LEFT JOIN `contracts` ON
            (
                `jobs`.`contract_id` = `contracts`.`id`
            )
        )
    LEFT JOIN `properties` ON
        (
            `jobs`.`property_id` = `properties`.`id`
        )
    )
WHERE
    `job_docs`.`doc_access` = 'External' AND `document_types`.`is_visible_on_portal` = 'Yes' AND `document_types`.`status` = 'A' AND `job_docs`.`deleted_at` IS NULL AND `document_types`.`deleted_at` IS NULL AND `jobs`.`deleted_at` IS NULL AND `contracts`.`deleted_at` IS NULL AND `properties`.`deleted_at` IS NULL

EXPLAIN SELECT * FROM jobs_all_documents

enter image description here


[Edit]

I created views from phpmyadmin. Parenthesis is added by default. i tried to optimsed query by replacing left join with inner join, but after that execution time were increased by 14 sec.

  • execution time with left joins -> 29.86 sec for 1034006 records
  • execution time with Inner joins -> 41.65 sec for 1034006 records,
2
  • 1) Remove all excess parenthesis - MySQL is not MS Access and does not need in them. 2) Convert all LEFT JOIN to INNER JOIN if according table is mentioned in WHERE w/o IS NULL. 3) Optimize each separate subquery separately. 4)Provide complete CREATE TABLE for all mentioned tables.
    – Akina
    Commented Apr 7, 2023 at 12:00
  • Spare us some effort -- which lines are different between the two Selects being Unioned?
    – Rick James
    Commented Apr 7, 2023 at 22:53

4 Answers 4

0

Intermediate variant.

  1. Some LEFT JOIN converted to INNER JOIN.
  2. The query text is reformatted, excess parenthesis are removed.
  3. Output lists are removed for query shortening.
SELECT ...
FROM      `oapp_forms`
JOIN      `forms`          ON `oapp_forms`.`form_id` = `forms`.`id`
JOIN      `document_types` ON `forms`.`document_type_id` = `document_types`.`id`
LEFT JOIN `jobs`           ON `oapp_forms`.`job_id` = `jobs`.`id`
LEFT JOIN `contracts`      ON `jobs`.`contract_id` = `contracts`.`id`
LEFT JOIN `properties`     ON `jobs`.`property_id` = `properties`.`id`
WHERE `oapp_forms`.`form_status` IN('Verified', 'Auto Verified') 
  AND `oapp_forms`.`deleted_at` IS NULL 
  AND `forms`.`is_form` = 'Yes' 
  AND `document_types`.`is_visible_on_portal` = 'Yes' 
  AND `forms`.`deleted_at` IS NULL 
  AND `jobs`.`deleted_at` IS NULL 
  AND `properties`.`deleted_at` IS NULL 
  AND `document_types`.`deleted_at` IS NULL

UNION

SELECT ...
FROM      `job_docs`
JOIN      `document_types` ON `job_docs`.`doc_type_id` = `document_types`.`id`
LEFT JOIN `jobs`           ON job_docs`.`job_id` = `jobs`.`id`
LEFT JOIN `contracts`      ON `jobs`.`contract_id` = `contracts`.`id`
LEFT JOIN `properties`     ON `jobs`.`property_id` = `properties`.`id`
WHERE `job_docs`.`doc_access` = 'External' 
  AND `document_types`.`is_visible_on_portal` = 'Yes' 
  AND `document_types`.`status` = 'A' 
  AND `job_docs`.`deleted_at` IS NULL 
  AND `document_types`.`deleted_at` IS NULL 
  AND `jobs`.`deleted_at` IS NULL 
  AND `contracts`.`deleted_at` IS NULL 
  AND `properties`.`deleted_at` IS NULL

Complete DDLs is needed for optimization.

3
  • I created views from phpmyadmin. Parenthesis is added by default. i tried to optimise the query by replacing left join with inner join, but after that execution time was increased. execution time with left joins -> 29.86 sec for 1034006 records execution time with Inner joins -> 41.65 sec for 1034006 records
    – Zoe
    Commented Apr 8, 2023 at 7:01
  • @Zoe This means that either there are no optimal indices or the statistic is too inaccurate. Where are tables DDLs?
    – Akina
    Commented Apr 8, 2023 at 11:21
  • Run ANALYZE TABLE on each table. It may help.
    – Rick James
    Commented Apr 15, 2023 at 15:39
0

A crude pass suggests that these indexes may help:

properties:  INDEX(deleted_at, id,  address1, uprn, postcode)
document_types:  INDEX(is_visible_on_portal, deleted_at, status, id,  document_type_name)
forms:  INDEX(is_form, deleted_at, id,  form_code, document_type_id)
contracts:  INDEX(deleted_at, id,  client_document_name)
oapp_forms:  INDEX(form_status, deleted_at, form_id,  job_id)
jobs:  INDEX(deleted_at, id,  contract_id, property_id)
job_docs:  INDEX(doc_access, deleted_at, doc_type_id,  job_id)

(These are based on both your formulation and Akina's.)

4
  • Could you provide some other alternatives to Union All?
    – Zoe
    Commented Apr 8, 2023 at 9:00
  • UNION ALL -- which of the two SELECTs is slower? We can focus on that first.
    – Rick James
    Commented Apr 8, 2023 at 15:58
  • @if I run both select query individual , it is fine. Getting result in 0.5 sec. UNION ALL is making it slow
    – Zoe
    Commented Apr 15, 2023 at 5:53
  • The only case for that would be if the UNION had an ORDER BY and LIMIT. I don't see such in your query. OR... It is in a VIEW and there is a WHERE clause on the use of the view. (Views rarely, if ever, provide any performance, and may make performance worse.)
    – Rick James
    Commented Apr 15, 2023 at 15:38
0

Well, given your use case optimising select * from jobs_all_documents seems to be the wrong strategy, instead you want to:

  1. paginate that ~1M result set. You will need to display only a subset on the first page, if e.g. in the worst case that you have to display all docs. So what is the order by you‘re going to use? What are explain plans for statements with appropriate limit and order by clauses? If you don’t paginate, rendering times in the browser are going to kill you.
  2. avoid forcing a full result set: union is a no go, then, as it needs to read all rows for duplicate elimination, before returning the first row. If possible get rid of the union all.
  3. Choose your access path wisely. E.g. I would expect that the number of elements in job_docs needs to be returned, at max. It seems you‘re showing some 1:N depended attributes. If you do, you may want to split that off in your application code and use a second statement hopefully using foreign key indexes on these tables. Make your application code load that additional info lazily.

In addition:

Depending on the sizes of the tables you may need to employ an „index read“ strategy, in which you put dependent attributes into an index, so that you can entirely avoid scanning the table and all info is read from the index instead.

0

Probably you can change all the outer joins to inner joins, since they are pk-fk joins. But i am not sure whether outer2inner conversion can help to achieve better performance, because there is no local filter predicates on parent tables(except for document_types). The remaining measure i can see is designing proper index indexes which is kind of extensive(5 joins), see the diagnosis details(will have better diagnosis result if ddl provided).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.