Optimise View in MariaDB

Question

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

---

[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,

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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