Как я могу оптимизировать этот вид сложного SQL -запроса лот левых соединений и подзадных, сортируя поля, занимая более ⇐ MySql

[Anonymous]

В настоящее время у меня есть запрос, аналогичный ниже одному, но у него слишком много левых соединений, подложки.
Left Joins, потому что мы должны использовать их для фильтрации, где предложение.
Subqueries, потому что существует 1: M-отношения. < /p>
-> Сортировка по этим полям. 8 < /p>
Может ли кто -нибудь указать на мое, что я делаю неправильно, и как я могу попытаться исправить и оптимизировать этот сложный запрос.SELECT
p.id AS projectId,
p.title AS projectTitle,
p.projectImage AS projectImageUrl,
p.description AS projectDescription,
p.mainSiteId AS mainSiteId,
s.name AS mainSiteName,
(
SELECT COUNT(*)
FROM project_updates pu
WHERE pu.projectId = p.id
AND DATE(pu.createdAt) = CURRENT_DATE()
) AS dailyUpdateCount,
(
CASE
WHEN p.lastMilestone IS NOT NULL OR
EXISTS (
SELECT 1
FROM completed_tasks ct
WHERE ct.projectId = p.id AND ct.deletedAt IS NULL
)
THEN true
ELSE false
END
) AS previouslyCompleted,
(
SELECT
project_social_media.url
FROM
project_social_media
WHERE project_social_media.projectId = p.id
AND project_social_media.type = 'website'
LIMIT 1
) AS website,
a.name AS area,
t.id AS typeId,
t.name AS typeName,
c.name AS clientName,
p.lastContacted AS lastContacted,
p.lastContactMedium AS lastContactMedium,
p.lastMilestone AS lastMilestone,
(
SELECT
(
SELECT
COUNT(*)
FROM task_assignments ta
WHERE ta.projectId = p.id
AND ta.stage = 'COMPLETED'
) + (
SELECT
COUNT(*)
FROM completed_tasks ct
WHERE ct.projectId = p.id
AND ct.deletedAt IS NULL
)
) AS noOfCompletedTasks,
p.budget AS budget,
(
SELECT
COUNT(*)
FROM project_contacts pc
WHERE pc.projectId = p.id
AND pc.deletedAt IS NULL
AND pc.contractorId IS NULL
) AS noOfNodesInOrgChart,
(
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', project_members.userId,
'name', CONCAT(u.firstName, ' ', u.lastName),
'profileImageUrl', u.profileImage,
'departmentId', dept.id,
'department', dept.name,
'lastActiveOn', DATE_FORMAT(u.lastActiveOn, '%Y-%m-%dT%H:%i:%S.%fZ'),
'contactType', contact_type.name,
'contactTypeId', project_members.contactTypeId,
'category', project_category.name,
'categoryId', project_members.categoryId,
'engagement', project_engagement.name,
'engagementId', project_engagement.id
)
)
FROM
project_members
INNER JOIN user u ON project_members.userId = u.id
LEFT JOIN department dept ON u.departmentId = dept.id
LEFT JOIN contact_type ON project_members.contactTypeId = contact_type.id
LEFT JOIN project_category ON project_members.categoryId = project_category.id
LEFT JOIN project_engagement ON p.engagement = project_engagement.id
WHERE project_members.isRemoved = false
AND u.deactivated = false
AND project_members.projectId = p.id
) AS projectMembers,
NULL AS coProjectMembers,
secondarySite.name AS secondarySiteName,
secondarySite.id AS secondarySiteId,
(
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', project_social_media.id,
'type', project_social_media.type,
'url', project_social_media.url
)
)
FROM
project_social_media
WHERE project_social_media.projectId = p.id
) AS socialMedia,
contact_type.id AS projectContactTypeId,
contact_type.name AS projectContactType,
project_category.id AS projectCategoryId,
project_category.name AS projectCategory,
project_engagement.id AS projectEngagementId,
project_engagement.name AS projectEngagement,
(
CASE
WHEN p.lastMilestone IS NOT NULL OR
EXISTS (
SELECT 1
FROM completed_tasks ct
WHERE ct.projectId = p.id AND ct.deletedAt IS NULL
)
THEN true
ELSE false
END
) AS previouslyCompleted,
false AS isRemoved,
(
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', project_contacts.id,
'name', project_contacts.name,
'role', project_contacts.title,
'email', project_contacts.email,
'linkedInUrl', project_contacts.linkedInUrl,
'phoneNumbers', (
SELECT
JSON_ARRAYAGG(
JSON_OBJECT(
'id', project_contacts_phone_numbers.id,
'type', project_contacts_phone_numbers.type,
'phone', project_contacts_phone_numbers.phone,
'phoneCC', project_contacts_phone_numbers.phoneCC,
'isWhatsApp', project_contacts_phone_numbers.isWhatsApp
)
)
FROM
project_contacts_phone_numbers
WHERE project_contacts_phone_numbers.projectContactId = project_contacts.id
)
)
)
FROM
project_contacts
WHERE
project_contacts.projectId = p.id
AND project_contacts.deletedAt IS NULL
) AS contacts,
(
SELECT
COUNT(DISTINCT task.id)
FROM
task
INNER JOIN task_inventory ON task.id = task_inventory.taskId
INNER JOIN task_inventory_project_assignments
ON task_inventory.id = task_inventory_project_assignments.tiId
AND task_inventory_project_assignments.projectId = p.id
AND task_inventory_project_assignments.assignStatus = 'ASSIGNED'
WHERE
NOW()

Я попытался перемещать некоторые подборы в левые соединения (не будет большой разницей.)>

Подробнее здесь: https://stackoverflow.com/questions/797 ... d-subquery