Ticket Listing API call in customer portal is taking too much time to load

[sreekuttan-m-achari]

Ticket listing API call in customer portal is taking too much time to load and often results in timeouts. While checking from mysql terminal we have found this particular query is running for too much time.

If u check the following test run, it took more than 7 minutes to execute a single query.

mysql> show full processlist; +-------+--------+-----------------+--------------+---------+------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-------+--------+-----------------+--------------+---------+------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 15943 | devops | localhost:54170 | diamantedesk | Sleep | 1199 | | NULL | | 15944 | devops | localhost:54172 | diamantedesk | Sleep | 0 | | NULL | | 15945 | devops | localhost:54174 | diamantedesk | Sleep | 1199 | | NULL | | 15946 | devops | localhost:54176 | diamantedesk | Sleep | 1199 | | NULL | | 16299 | devops | localhost | diamantedesk | Query | 0 | starting | show full processlist | | 16421 | devops | localhost:55472 | diamantedesk | Sleep | 30 | | NULL | | 16422 | devops | localhost:55474 | diamantedesk | Query | 30 | Sending data | SELECT COUNT(d0_.id) AS sclr_0 FROM diamante_ticket d1_, diamante_ticket d0_ LEFT JOIN diamante_watcher_list d2_ ON d0_.id = d2_.ticket_id WHERE d0_.reporter_id = 'diamante_90' OR d2_.user_type = 'diamante_90' | | 16423 | devops | localhost:55476 | diamantedesk | Sleep | 30 | | NULL | | 16424 | devops | localhost:55478 | diamantedesk | Sleep | 30 | | NULL | +-------+--------+-----------------+--------------+---------+------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.00 sec)

`
mysql> SELECT COUNT(d0_.id) AS sclr_0 FROM diamante_ticket d1_, diamante_ticket d0_ LEFT JOIN diamante_watcher_list d2_ ON d0_.id = d2_.ticket_id WHERE d0_.reporter_id = 'diamante_90' OR d2_.user_type = 'diamante_90'
-> ;

+--------+
| sclr_0 |
+--------+
| 74200 |
+--------+
1 row in set (7 min 5.71 sec)

mysql> `