MyBB Community Forums

Full Version: Too many queries on showthread?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi,

even though I don't have that many plugins installed (Button Check, Fit On Page, My Advertisements, Overview, Spoiler BBCode, Thumbs Post Rating) I get 43 queries on a showthread page with 14 replies on one page.

On a page with zero replies I get 26 queries, on a page with 2 replies I get 28 queries and on a page with 4 replies I get 30 queries.

My forum is on local host and has 2 registered users.

Are these numbers normal?
On a vanilla install with no replies, I get 19 queries.
That's definitely not normal in a vanilla install. The number of queries on the page should not be affected by the number of posts unless there's a plugin running on a postbit hook that runs a query each time. I haven't looked at any of the plugins you've got installed unfortunately, so it's hard to say which it is.
Just to be sure I understand this correctly, a reply always does 1 query, right?

Query = Normal number + number of replies

Which means if I have lets say 20 queries on showthread without a reply, I would get 30 queries with 10 replies?

Edit:

(2012-04-25, 04:21 PM)euantor Wrote: [ -> ]That's definitely not normal in a vanilla install. The number of queries on the page should not be affected by the number of posts unless there's a plugin running on a postbit hook that runs a query each time. I haven't looked at any of the plugins you've got installed unfortunately, so it's hard to say which it is.

The thump post rating plugin puts on each reply a thumps up/thumps down image. Can this be the cause?
It may be if it isn't well written. Could you possibly post your full debug stats from the showthread?
What the hell, I think I know what caused this. When I disabled the MyAdvertisements plugin, the queries went down to 27 on the page with 14 replies and to 24 on any other page.

(2012-04-25, 04:33 PM)euantor Wrote: [ -> ]It may be if it isn't well written. Could you possibly post your full debug stats from the showthread?

Here you go, these are the debug stats from the page with 27 queries:
MyBB Debug Information
Page Generation
Page Generation Statistics
Page Generation Time: 	0.39695501327515 seconds 	No. DB Queries: 	27
PHP Processing Time: 	0.3020008 seconds (76.08%) 	DB Processing Time: 	0.0949543 seconds (23.92%)
Extensions Used: 	mysqli, xml 	Global.php Processing Time: 	0.1881549 seconds
PHP Version: 	5.3.8 	Server Load: 	Unbekannt
GZip Encoding Status: 	Disabled 	No. Templates Used: 	90 (99 Cached / 1 Manually Loaded)
Memory Usage: 	5,75 MB (6029312 bytes) 	Memory Limit: 	128M
Database Connections (1 Total)
[READ] root@localhost (Connected in 0s)

Database Queries (27 Total)
#1 - Write Query
SET NAMES 'utf8'
Query Time: 0.0001521110534668

#2 - Select Query
SELECT title,cache FROM mybb_datacache
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_datacache 	ALL 					33 	
Query Time: 0.0018868446350098

#3 - Select Query
SELECT * FROM mybb_sessions WHERE sid='e988d84cadae8b3649a171a1dff845b7' AND ip='127.0.0.1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_sessions 	system 	PRIMARY,ip 				1 	
Query Time: 0.00048398971557617

#4 - Select Query
SELECT u.*, f.* FROM mybb_users u LEFT JOIN mybb_userfields f ON (f.ufid=u.uid) WHERE u.uid='1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
u 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	
f 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	
Query Time: 0.00068187713623047

#5 - Select Query
SELECT * FROM mybb_threads WHERE tid='1'
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_threads 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	
Query Time: 0.00047993659973145

#6 - Write Query
UPDATE mybb_sessions SET `uid`='1', `time`='1335372023', `location`='/showthread.php?tid=1&debug=1', `useragent`='Mozilla/5.0 (Windows; U; Windows NT 5.1; de; rv:1.9.2.28) Gecko/20120306 Firefox/3.6.28 ( .NET CLR 3', `location1`='4', `location2`='1', `nopermission`='0' WHERE sid='e988d84cadae8b3649a171a1dff845b7' LIMIT 1
Query Time: 0.00044584274291992

#7 - Select Query
SELECT fid FROM mybb_threads WHERE tid = '1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_threads 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	
Query Time: 0.00045990943908691

#8 - Select Query
SELECT name, tid, properties, stylesheets FROM mybb_themes WHERE def='1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_themes 	ALL 					4 	Using where
Query Time: 0.00039792060852051

#9 - Select Query
SELECT title,template FROM mybb_templates WHERE title IN ('' ,'showthread' ,'postbit' ,'postbit_author_user' ,'postbit_author_guest' ,'showthread_newthread' ,'showthread_newreply' ,'showthread_newreply_closed' ,'postbit_sig' ,'showthread_newpoll' ,'postbit_avatar' ,'postbit_profile' ,'postbit_find' ,'postbit_pm' ,'postbit_www' ,'postbit_email' ,'postbit_edit' ,'postbit_quote' ,'postbit_report' ,'postbit_signature' ,'postbit_online' ,'postbit_offline' ,'postbit_away' ,'postbit_gotopost' ,'showthread_ratethread' ,'showthread_inline_ratethread' ,'showthread_moderationoptions' ,'multipage_prevpage' ,'multipage_nextpage' ,'multipage_page_current' ,'multipage_page' ,'multipage_start' ,'multipage_end' ,'multipage' ,'postbit_editedby' ,'showthread_similarthreads' ,'showthread_similarthreads_bit' ,'postbit_iplogged_show' ,'postbit_iplogged_hiden' ,'showthread_quickreply' ,'forumjump_advanced' ,'forumjump_special' ,'forumjump_bit' ,'showthread_multipage' ,'postbit_reputation' ,'postbit_quickdelete' ,'postbit_attachments' ,'thumbnails_thumbnail' ,'postbit_attachments_attachment' ,'postbit_attachments_thumbnails' ,'postbit_attachments_images_image' ,'postbit_attachments_images' ,'postbit_posturl' ,'postbit_rep_button' ,'postbit_inlinecheck' ,'showthread_inlinemoderation' ,'postbit_attachments_thumbnails_thumbnail' ,'postbit_quickquote' ,'postbit_qqmessage' ,'postbit_ignored' ,'postbit_groupimage' ,'postbit_multiquote' ,'showthread_search' ,'postbit_warn' ,'postbit_warninglevel' ,'showthread_moderationoptions_custom_tool' ,'showthread_moderationoptions_custom' ,'showthread_inlinemoderation_custom_tool' ,'showthread_inlinemoderation_custom' ,'postbit_classic' ,'showthread_classic_header' ,'showthread_poll_resultbit' ,'showthread_poll_results' ,'showthread_usersbrowsing' ,'showthread_usersbrowsing_user' ,'multipage_page_link_current' ,'multipage_breadcrumb' ,'postbit_tpr' ,'css' ,'headerinclude' ,'header' ,'footer' ,'gobutton' ,'htmldoctype' ,'header_welcomeblock_member' ,'header_welcomeblock_guest' ,'header_welcomeblock_member_admin' ,'global_pm_alert' ,'global_unreadreports' ,'' ,'global_pending_joinrequests' ,'nav' ,'nav_sep' ,'nav_bit' ,'nav_sep_active' ,'nav_bit_active' ,'footer_languageselect' ,'header_welcomeblock_member_moderator' ,'redirect' ,'error') AND sid IN ('-2','-1','2') ORDER BY sid ASC
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_templates 	ALL 					845 	Using where; Using filesort
Query Time: 0.004953145980835

#10 - Select Query
SELECT COUNT(uid) as total FROM mybb_joinrequests WHERE gid IN (0,4,8)
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
							Impossible WHERE noticed after reading const tables
Query Time: 0.00042104721069336

#11 - Select Query
SELECT threads, unapprovedthreads FROM mybb_forums WHERE fid = '4' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_forums 	const 	PRIMARY 	PRIMARY 	2 	const 	1 	
Query Time: 0.00044798851013184

#12 - Select Query
SELECT COUNT(tid) as threads FROM mybb_threads WHERE fid = '4' AND (lastpost >= '1334489892' OR sticky=1) AND (visible='1' OR visible='0') ORDER BY lastpost DESC
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_threads 	ref 	fid,lastpost 	fid 	2 	const 	8 	Using where
Query Time: 0.0014839172363281

#13 - Select Query
SELECT * FROM mybb_polls WHERE pid='1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_polls 	system 	PRIMARY 				1 	
Query Time: 0.00056099891662598

#14 - Select Query
SELECT * FROM mybb_pollvotes WHERE uid='1' AND pid='1'
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_pollvotes 	ref 	pid 	pid 	8 	const,const 	1 	
Query Time: 0.00038886070251465

#15 - Write Query
REPLACE INTO mybb_threadsread (tid, uid, dateline) VALUES('1', '1', '1335372023')
Query Time: 0.006187915802002

#16 - Select Query
SELECT COUNT(t.tid) AS unread_count FROM mybb_threads t LEFT JOIN mybb_threadsread tr ON (tr.tid=t.tid AND tr.uid='1') LEFT JOIN mybb_forumsread fr ON (fr.fid=t.fid AND fr.uid='1') WHERE t.visible=1 AND t.closed NOT LIKE 'moved|%' AND t.fid IN (4) AND t.lastpost > IFNULL(tr.dateline,1334767223) AND t.lastpost > IFNULL(fr.dateline,1334767223) AND t.lastpost > 1334767223
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
t 	ref 	fid,lastpost 	fid 	6 	const,const 	7 	Using where
tr 	eq_ref 	tid 	tid 	8 	forum.t.tid,const 	1 	Using where
fr 	const 	fid 	fid 	8 	const,const 	1 	Using where
Query Time: 0.029181003570557

#17 - Write Query
DELETE TTR.* FROM mybb_threadsread AS TTR INNER JOIN mybb_threads AS TT ON TT.tid = TTR.tid WHERE TTR.uid = '1' AND TT.fid IN (4)
Query Time: 0.002349853515625

#18 - Select Query
SELECT COUNT(*) AS replies FROM mybb_posts p WHERE p.tid='1' AND (p.visible='0' OR p.visible='1')
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
p 	ref 	tid,visible 	tid 	4 	const 	15 	Using where
Query Time: 0.00078010559082031

#19 - Select Query
SELECT p.pid FROM mybb_posts p WHERE p.tid='1' AND (p.visible='0' OR p.visible='1') ORDER BY p.dateline LIMIT 0, 15
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
p 	ref 	tid,visible 	tid 	4 	const 	15 	Using where; Using filesort
Query Time: 0.00069284439086914

#20 - Select Query
SELECT * FROM mybb_attachments WHERE pid IN('1','2','3','4','5','7','9','10','11','12','13','14','34','35','36')
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_attachments 	ALL 	pid 				25 	Using where
Query Time: 0.00074601173400879

#21 - Select Query
SELECT u.*, u.username AS userusername, p.*, f.*, eu.username AS editusername FROM mybb_posts p LEFT JOIN mybb_users u ON (u.uid=p.uid) LEFT JOIN mybb_userfields f ON (f.ufid=u.uid) LEFT JOIN mybb_users eu ON (eu.uid=p.edituid) WHERE pid IN('1','2','3','4','5','7','9','10','11','12','13','14','34','35','36') ORDER BY p.dateline
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
p 	ALL 	PRIMARY 				37 	Using where; Using filesort
u 	eq_ref 	PRIMARY 	PRIMARY 	4 	forum.p.uid 	1 	
f 	eq_ref 	PRIMARY 	PRIMARY 	4 	forum.u.uid 	1 	
eu 	eq_ref 	PRIMARY 	PRIMARY 	4 	forum.p.edituid 	1 	
Query Time: 0.0018551349639893

(Plugin Hook: postbit)
#22 - Select Query
SELECT pid,thumbsup,thumbsdown FROM mybb_thumbspostrating WHERE uid=1 AND pid IN('1','2','3','4','5','7','9','10','11','12','13','14','34','35','36')
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
							Impossible WHERE noticed after reading const tables
Query Time: 0.00044012069702148

#23 - Select Query
SELECT template FROM mybb_templates WHERE title='postbit_attachments_attachment_unapproved' AND sid IN ('-2','-1','2') ORDER BY sid DESC LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_templates 	ALL 					845 	Using where; Using filesort
Query Time: 0.0021908283233643

#24 - Select Query
SELECT t.*, t.username AS threadusername, u.username, MATCH (t.subject) AGAINST ('Hello World') AS relevance FROM mybb_threads t LEFT JOIN mybb_users u ON (u.uid = t.uid) WHERE t.fid='4' AND t.tid!='1' AND t.visible='1' AND t.closed NOT LIKE 'moved|%' AND MATCH (t.subject) AGAINST ('Hello World') >= '10' ORDER BY t.lastpost DESC LIMIT 0, 5
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
t 	fulltext 	PRIMARY,fid,subject 	subject 	0 		1 	Using where; Using filesort
u 	eq_ref 	PRIMARY 	PRIMARY 	4 	forum.t.uid 	1 	
Query Time: 0.00079202651977539

#25 - Select Query
SELECT pid FROM mybb_posts WHERE tid='1' ORDER BY pid DESC LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
mybb_posts 	index 	tid 	PRIMARY 	4 		2 	Using where
Query Time: 0.00043296813964844

#26 - Select Query
SELECT tid FROM mybb_threadsubscriptions WHERE tid='1' AND uid='1' LIMIT 1
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
							Impossible WHERE noticed after reading const tables
Query Time: 0.00040006637573242

#27 - Select Query
SELECT s.ip, s.uid, s.time, u.username, u.invisible, u.usergroup, u.displaygroup FROM mybb_sessions s LEFT JOIN mybb_users u ON (s.uid=u.uid) WHERE s.time > '1335371123' AND location2='1' AND nopermission != 1 ORDER BY u.username ASC, s.time DESC
table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
s 	system 	location2,time 				1 	
u 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	
Query Time: 0.00049304962158203

Template Statistics
Templates Used (Loaded for this Page) - 90 Total
global_pm_alert, header_welcomeblock_member, header_welcomeblock_member_moderator, postbit_online, showthread_moderationoptions, showthread_moderationoptions_custom, showthread_moderationoptions_custom_tool, showthread_inlinemoderation_custom, showthread_inlinemoderation_custom_tool, showthread_inlinemoderation, showthread_classic_header, showthread, showthread_usersbrowsing, showthread_usersbrowsing_user, showthread_ratethread, forumjump_advanced, header_welcomeblock_member_admin, redirect, gobutton, postbit_away, footer, headerinclude, showthread_similarthreads, nav_sep, nav_sep_active, header_welcomeblock_guest, header, showthread_newreply_closed, postbit_edit, postbit_email, postbit_find, postbit_pm, postbit_quickdelete, postbit_quote, postbit_rep_button, postbit_report, postbit_www, showthread_poll_resultbit, postbit_avatar, showthread_quickreply, postbit, postbit_classic, postbit_gotopost, postbit_groupimage, showthread_newreply, showthread_newthread, showthread_poll_results, global_unreadreports, global_pending_joinrequests, multipage_breadcrumb, multipage, multipage_end, multipage_nextpage, multipage_page, multipage_page_current, multipage_page_link_current, multipage_prevpage, multipage_start, postbit_attachments_thumbnails, postbit_posturl, postbit_attachments_thumbnails_thumbnail, postbit_attachments_images_image, postbit_signature, postbit_attachments, postbit_attachments_attachment, postbit_attachments_images, postbit_reputation, showthread_multipage, postbit_iplogged_hiden, postbit_iplogged_show, forumjump_bit, forumjump_special, showthread_similarthreads_bit, postbit_offline, postbit_editedby, error, nav, nav_bit, nav_bit_active, postbit_inlinecheck, htmldoctype, postbit_multiquote, postbit_author_user, postbit_author_guest, showthread_search, postbit_ignored, footer_languageselect, postbit_warninglevel, postbit_warn, postbit_attachments_attachment_unapproved

Templates Requiring Additional Calls (Not Cached at Startup) - 1 Total
postbit_attachments_attachment_unapproved
It doesn't seem to be the thump post rating plugin. It may be MyAdvertisements though if yu're seeing a decrease of that size when you disable it. The thread with the more queries, does it happen to have a poll? From the debug stats, it looks like it does.
Yes it's not the Thumps Post Rating plugin. It only does 1 query. It's in fact the MyAdvertisements plugin. Yes the thread has a poll. Guess this explains it then Smile