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> SELECT COUNT(d0_.id) AS sclr_0 FROM diamanteticket d1, diamanteticket d0 LEFT JOIN diamante_watcherlist d2 ON d0.id = d2.ticketid WHERE d0.reporter_id = 'diamante90' OR d2.user_type = 'diamante_90'
-> ;
+--------+
| sclr_0 |
+--------+
| 74200 |
+--------+
1 row in set (7 min 5.71 sec)
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 diamanteticket d1, diamanteticket d0 LEFT JOIN diamante_watcherlist d2 ON d0.id = d2.ticketid WHERE d0.reporter_id = 'diamante90' OR d2.user_type = 'diamante_90' -> ;
+--------+ | sclr_0 | +--------+ | 74200 | +--------+ 1 row in set (7 min 5.71 sec)
mysql> `