Question Slow Query

[Zuker]

I ve got this query getting my forum so slow.

SELECT COUNT(*) AS count, contentid, varname FROM dbtech_thanks_entry AS entry, (SELECT postid FROM post WHERE threadid = 80911 AND visible IN (1) ) AS tmp WHERE entry.contenttype = 'post' AND entry.varname = 'likes' AND contentid = tmp.postid GROUP BY contentid ORDER BY count DESC LIMIT 5

This its whay happened to my db after disabling my plugin when site was crashing.


Any ideas?

Thanks

 

[Fillip H.]

I don't have a large database to test against so I'm not sure if there's anything I can do on my end without some experimentation on your end

If you run that query manually, but adding EXPLAIN in front of it, what does it say?

 

[Zuker]

ariaDB [turiver]> explain SELECT COUNT(*) AS count, contentid, varname FROM dbtech_thanks_entry AS entry, (SELECT postid FROM post WHERE threadid = 80910 AND visible IN (1) ) AS tmp WHERE entry.contenttype = 'post' AND entry.varname = 'likes' AND contentid = tmp.postid GROUP BY contentid ORDER BY count DESC LIMIT 5;
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+------+----------------------------------------------+
| 1 | SIMPLE | post | ref | PRIMARY,threadid,threadid_visible_dateline | threadid_visible_dateline | 6 | const,const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | entry | ref | entryid_3,content | content | 81 | turiver.post.postid,const | 3 | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

 

[Zuker]

MariaDB [turiver]> explain SELECT COUNT(*) AS count, contentid, varname FROM dbtech_thanks_entry AS entry, (SELECT postid FROM post WHERE threadid = 80911 AND visible IN (1) ) AS tmp WHERE entry.contenttype = 'post' AND entry.varname = 'likes' AND contentid = tmp.postid GROUP BY contentid ORDER BY count DESC LIMIT 5;
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+--------+----------------------------------------------+
| 1 | SIMPLE | post | ref | PRIMARY,threadid,threadid_visible_dateline | threadid_visible_dateline | 6 | const,const | 126905 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | entry | ref | entryid_3,content | content | 81 | turiver.post.postid,const | 3 | Using index condition; Using where |
+------+-------------+-------+------+--------------------------------------------+---------------------------+---------+---------------------------+--------+----------------------------------------------+
2 rows in set (0.00 sec)

 

[Fillip H.]

I see, over 126k rows for a single thread. I think your best bet will be to disable the thread and forum blocks via the "Display Button Click Count - Threads" and "Display Button Click Count - Forums" settings respectively.

 

[DragonByte Technologies]

Hello @Zuker,

We hope your ticket regarding Advanced Post Thanks / Like has been addressed to your satisfaction. This ticket has now been closed.

If your ticket has not been resolved, you can reply to this thread at any point in the next 7 days in order to reopen the ticket, afterwards this thread will be closed.

Please do not reply to this thread if your ticket has been resolved.

Thank you.


- DragonByte Technologies, Ltd.