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
------------------------------------------------
[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,
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,
Asked by Zoe
(11 rep)
Apr 7, 2023, 08:36 AM
Last activity: May 22, 2025, 12:09 AM
Last activity: May 22, 2025, 12:09 AM