I'm having problems with my application performance.
The tables in the database have the following records:
"issues" has more than 334823
"custom_values" has over 1350243
"users" has 1350
"projects" 494
The system currently has 1350 users. Currently only goes to 100 concurrent users.
The application is being way too slow.
The slowest query is as below and take about 15 seconds:
SELECT `issues`.*
FROM `issues`
INNER JOIN `projects` ON `projects`.`id` = `issues`.`project_id`
WHERE (((projects.status <> 9
AND EXISTS
(SELECT 1 AS one
FROM enabled_modules em
WHERE em.project_id = projects.id
AND em.name = 'issue_tracking'))
AND (((projects.is_public = 1
AND projects.id NOT IN
(SELECT project_id
FROM members
WHERE user_id IN (44,
638)))
AND ((issues.is_private = 0
OR issues.author_id = 44
OR issues.assigned_to_id IN (44, 186, 898))))
OR (projects.id IN (1, 6, 9, 13, 115, 129,
131, 136, 186, 227, 228, 230, 275, 287,
315, 335, 340, 369, 394, 404, 408,
450, 453, 454, 456, 458,460,
462,
492,
511,
512,
516,
519,
527,
531,
533,
537)
AND ((issues.is_private = 0
OR issues.author_id = 44
OR issues.assigned_to_id IN (44,
186,
898))))
OR (projects.id IN (40,
68,
130,
149,
326,
330,
331,
332,
333,
350,
356,
402,
455,
514,
515,
528)
AND (1 = 1))
OR (projects.id IN (91, 111, 128, 130, 149, 235, 257, 322, 326, 328, 330, 331, 332, 333, 350, 356, 489, 508, 514, 515, 525, 528)
AND ((issues.is_private = 0
OR issues.author_id = 44
OR issues.assigned_to_id IN (44,
186,
898))))
OR (projects.id IN (457)
AND ((issues.is_private = 0
OR issues.author_id = 44
OR issues.assigned_to_id IN (44,
186,
898)))))))
AND (`issues`.`id` != 251880)
AND (LOWER(issues.subject) LIKE LOWER('%255392%'))
ORDER BY `issues`.`id` DESC
LIMIT 10
The tabular explanation is also attached. Can somebody suggest me the techniques to optimize sql queries?
Best Answer
These defy optimization:
OR
,NOT IN ( SELECT ... )
,<>
. See if you can reformulate the query to avoid some of those.The only hope I see is in
AND (LOWER(issues.subject) LIKE LOWER('%255392%'))
-- Can you turn that into aFULLTEXT
search?LOWER
is avoidable via using a case-insensitive collation. (We can't see the collation forsubject
.)