Announcement

Collapse
No announcement yet.

Trying to Identify Query that Causes Slow DB Running

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Trying to Identify Query that Causes Slow DB Running

    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
    Last edited by Banana; Thu 25th Oct '18, 2:39pm.

Related Topics

Collapse

Working...
X