0

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?tabular explain issue table create projects table create enable_modules table create members table create

4
  • Can you include SHOW CREATE TABLE {tablename} for each table here? Where do these large lists of project numbers come from? Why is LOWER called on a regex that doesn't contain letters? How is changing issues.subject to lowercase going to change its match? Do you know how to use JOIN? Welcome to DBA Stack Exchange. Good first question.
    – danblack
    Commented Dec 18, 2019 at 5:00
  • Thanks for the reply. I have updated the question with screenshots for each table. These large list of project ids are from Ruby's ORM ActiveRecord. The ORM firstly loads the projects & then loads all of the records referenced in Issue table. Why is LOWER called on a regex that doesn't contain letters? How is changing issues.subject to lowercase going to change its match? Didn't quite understand the questions here. Commented Dec 18, 2019 at 7:56
  • What's with all those brackets???
    – Eric
    Commented Dec 19, 2019 at 17:55
  • 1
    One thing to note is subqueries typically are slower than just joining directly on a table, and I see you subquery and then subquery within the subquery. If i was you, I would try to rewrite the query first and foremost buy trying to turn that first subquery instead into a join, and then repeat for the nested subquery and so on. You should also then be able to put those where conditions from your subqueries either into your now single queries where clause or in the ON statement of your joins. Then look at your query execution if it is still slow.
    – bjk116
    Commented Dec 19, 2019 at 19:09

1 Answer 1

1

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 a FULLTEXT search? LOWER is avoidable via using a case-insensitive collation. (We can't see the collation for subject.)

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.