Hi
It has been identified that the following query is being run frequently on our database causing it to run very slowly when load is higher than normal
SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1518365046 AND dateline > IF(threadread.readtime IS NULL, 1538597345, threadread.readtime)
Is it possible to know which feature is causing this query to run please?
Here's my current process list from mysql for example
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 126736 | admin | localhost | vb_***** | Sleep | 800 | | NULL | 0.000 |
| 129303 | admin | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
| 129331 | ***| localhost | ***| Query | 1 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688876, threadread.readtime) | 0.000 |
| 129332 | ***| localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
| 129333 | *** | localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
| 129334 | *** | localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
Thanks
It has been identified that the following query is being run frequently on our database causing it to run very slowly when load is higher than normal
SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1518365046 AND dateline > IF(threadread.readtime IS NULL, 1538597345, threadread.readtime)
Is it possible to know which feature is causing this query to run please?
Here's my current process list from mysql for example
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| 126736 | admin | localhost | vb_***** | Sleep | 800 | | NULL | 0.000 |
| 129303 | admin | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
| 129331 | ***| localhost | ***| Query | 1 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688876, threadread.readtime) | 0.000 |
| 129332 | ***| localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
| 129333 | *** | localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
| 129334 | *** | localhost | *** | Query | 0 | Sending data | SELECT COUNT(*) AS count FROM post AS post LEFT JOIN threadread AS threadread ON (threadread.threadid = post.threadid AND threadread.userid = 0) WHERE dateline >= 1535749074 AND dateline > IF(threadread.readtime IS NULL, 1538688877, threadread.readtime) | 0.000 |
+--------+-------------+-----------+------------+---------+------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
Thanks