We have a few slow queries in forums. I am trying to track them down and see if I can fix them somehow. Here is an example from our mysql-slow.log:
Code:
# Time: 090929 9:04:54
# User@Host: unixdb[unixdb] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 10 Rows_examined: 75157
SELECT thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.views, thread.visible, thread.open, user.username, user.userid, user.usergroupid, user.displaygroupid
FROM thread AS thread
LEFT JOIN user AS user ON (user.username = thread.lastposter)
WHERE NOT ISNULL(thread.threadid) AND thread.visible = '1' AND thread.open!='10' AND thread.forumid NOT IN(39,128,99,59,87,126,92,95,65,73,114,78,41,133,68,123,74,115,75,66,58,76,94,64,72,80,111,63,103,79,105,62,31,58,59,62,64,65,66,67,68,69,72,73,74,75,76,78,80,90,91,92,94,95,97,98,99,102,103,105,106, 107, 108,110,111,112,113,115)
ORDER BY lastpost DESC
LIMIT 0, 10;
# User@Host: unixdb[unixdb] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 10 Rows_examined: 75157
SELECT thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.views, thread.visible, thread.open, user.username, user.userid, user.usergroupid, user.displaygroupid
FROM thread AS thread
LEFT JOIN user AS user ON (user.username = thread.lastposter)
WHERE NOT ISNULL(thread.threadid) AND thread.visible = '1' AND thread.open!='10' AND thread.forumid NOT IN(39,128,99,59,87,126,92,95,65,73,114,78,41,133,68,123,74,115,75,66,58,76,94,64,72,80,111,63,103,79,105,62,31,58,59,62,64,65,66,67,68,69,72,73,74,75,76,78,80,90,91,92,94,95,97,98,99,102,103,105,106, 107, 108,110,111,112,113,115)
ORDER BY lastpost DESC
LIMIT 0, 10;
I have searched the templates in the database and the php code but I have yet to find what part of the PHP code base is executing this query.
Any ideas how to track this MySQL query down?
---------- Post updated at 10:13 ---------- Previous update was at 10:10 ----------
Here is another one:
Code:
# Time: 090929 8:44:25
# User@Host: unixdb[unixdb] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 26765
SELECT t.forumid, t.threadid, t.title, t.replycount, t.lastposter, t.lastpost
FROM thread AS t
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = t.threadid AND type = 'thread')
WHERE forumid = 30 AND lastpost < 1217920495 AND visible = 1 AND open <> 10
AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 1;
# User@Host: unixdb[unixdb] @ localhost []
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 26765
SELECT t.forumid, t.threadid, t.title, t.replycount, t.lastposter, t.lastpost
FROM thread AS t
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = t.threadid AND type = 'thread')
WHERE forumid = 30 AND lastpost < 1253204446 AND visible = 1 AND open <> 10
AND deletionlog.primaryid IS NULL
ORDER BY lastpost DESC
LIMIT 1;
# Time: 090929 8:44:27
# User@Host: unixdb[unixdb] @ localhost []
# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 26765
SELECT t.forumid, t.threadid, t.title, t.replycount, t.lastposter, t.lastpost
FROM thread AS t
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = t.threadid AND type = 'thread')
WHERE forumid = 30 AND lastpost > 1134582244 AND visible = 1 AND open <> 10
AND deletionlog.primaryid IS NULL
ORDER BY lastpost
LIMIT 1;
Suggestions to trace where these queries originate in the PHP code?
---------- Post updated at 11:15 ---------- Previous update was at 10:13 ----------
Follow up:
I found the MySQL query (one of them) searching the database using phpMyAdmin. An exact phrase search query did not match, but searching for all the words in the phrase matched a vBulletin plugin.
---------- Post updated at 11:32 ---------- Previous update was at 11:15 ----------
Note: I upgraded the Advanced Forum Statistics below the navbar on the home page of the forums to see if the latest version of this plugin solves the issue with the slow query.
Anyway, the latest version looks nicer
---------- Post updated at 11:45 ---------- Previous update was at 11:32 ----------
Ah Ha! The second slow query is coming from the vB plugin Prevent DoublePost. I'll search for a later version which might be better written (optimized).
Hi there,
I have a hierarchical database that include 4 tables. Table A is the parent of B, B is Parent of C, C is parent of D. If I want to query everything in D that is associated with A.name, how do I do that? Thanks!
YanYan (0 Replies)
In MySQL's slow queries log, it'll have an entry like this:
# User@Host: scc_service @
# Query_time: 43 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET timestamp=1237769209;
UPDATE loan SET funding_status="scheduled",datetime_approved=now() WHERE loan_id = '00000';
What does Lock_time... (2 Replies)
hello all
i have 2 columns every column in the following format
column1
2011-04-01 11:39:54
column2
2019-02-03 00:00:00
i want get difference between above data as following
2 days 11:39
how to do so ?
i tried many functions but nothing works
please advice what is the query... (6 Replies)
Hi,
I want to run below query on shellscript but having one problm.
ADV=$( mysql -h "$IP_ADDR" -u "$USER_NAME" "$TABLE_NAME" -BNe" SELECT ADV FROM indata where inid='$INSTRUID' and Date='$latest Date';" )
here Date column contans different below dates
2011-12-01... (0 Replies)
Hello
i have created mysql query to compare to values and get difference in percentage
as following:
SELECT file_name, 100 - ((100 * (SELECT file_count FROM xipi_files z WHERE x.file_group = z.file_group AND x.file_name = z.file_name AND z.insert_date = CURDATE( ) - INTERVAL 1 DAY)) /... (1 Reply)
Hi All,
i have a table in mysql with the following data
Table name Test
Assettype Serial_No Status location
Mouse 123456 In Stock chennai
Mouse 98765 Allocated chennai
Keyboard ... (2 Replies)
09-29-2009
