UBB.threads PHP Forum Software Community Forums UBB.threads 6 Version 6 Support 6.4.1 - painfully slow

Since I upgraded our Threads from 6.3.2 to 6.4.1, it has been painfully slow. Initially we were consistently seeing sub-second query times, but as the user load escalates (around 150 users on up), it is just dead - 40 seconds to generate the main page (56 queries). We see 25-40 secs to generate pages with <10queries. And some times, we see sub-second response.

I have optimized tables, tweaked my.cnf to maximize use of our 1GB of RAM. No other changes were made to the server since the upgrade, and the server load has been consistent since prior to the upgrade.

I would appreciate any other suggestions.

When logged in as an admin, visit this URL:

http://www.4x4wire.com/forums/ubbthreads.php?debug=1

You will see a list of the queries made to generate the page normally. Which of those take the longest to execute?

Hi Charles, nice to hear from you and thanks for the help.

I did this several times and got several sub-second page generation times. Then I got this:

Generated in 10.337 seconds in which 8.99 seconds were spent on a total of 24 queries. Zlib compression enabled.

Longest queries:

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 2 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.214 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 75 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.166 seconds.

Those two are identical? multiple instances of the same query?

The first one above is always the slowest, whenever we have the slowdown (which is happening a lot this afternoon).

Hmm, both of those queries were taking under a quarter of a second. Since it said the total mysql execution time was over 8 seconds are you sure there were no queries that took longer than that?

The queries aren't quite identical. Each of those queries grabs the forum information for forums within a certain category.

Hi Rick, here is a complete example. See the very last line. <img src="https://www.ubbcentral.com/boards/images/graemlins/confused.gif" alt="" />

Query: REPLACE INTO w3t_Online (O_Username , O_Uid , O_Last , O_What , O_Board , O_Extra , O_Read , O_Type) VALUES ('little_joe' , '87' , 'xxxxxxxxxxx' , 'ubbthreads' , '' , '' , '' , 'r')
Query took a total of 0.039 seconds.

Query: SELECT t1.Mod_Board , t1.Mod_Uid , t2.U_Username FROM w3t_Moderators AS t1 , w3t_Users AS t2 WHERE t1.Mod_Uid = t2.U_Number
table type possible_keys key key_len ref rows Extra
t1 index Modindx1 Modindx1 104 66 Using index
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Mod_Uid 1
Query took a total of 0.048 seconds.

Query: SELECT C_Users , C_NewUsername , C_NewUid , C_Users_timestamp , C_Birthdays , C_Birthdays_timestamp , C_MaxOnline , C_MaxOnline_timestamp FROM w3t_Cache
table type possible_keys key key_len ref rows Extra
w3t_Cache system 1
Query took a total of 0.039 seconds.

Query: SELECT L_Board , L_Last FROM w3t_Last WHERE L_Uid = '87'
table type possible_keys key key_len ref rows Extra
w3t_Last ref PRIMARY PRIMARY 4 const 23 where used
Query took a total of 0.016 seconds.

Query: SELECT O_Board , COUNT(*) , O_Type FROM w3t_Online WHERE O_Last > '1075757440' GROUP BY O_Board , O_Type
table type possible_keys key key_len ref rows Extra
w3t_Online ALL Oindx2 221 where used; Using temporary
Query took a total of 0.013 seconds.

Query: SELECT DISTINCT Cat_Title , Cat_Number , Cat_Description FROM w3t_Category WHERE Cat_Type = 'forum' ORDER BY Cat_Number
table type possible_keys key key_len ref rows Extra
w3t_Category ALL type_ndx 17 where used; Using temporary
Query took a total of 0.109 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 2 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.077 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 5 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.135 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 10 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 7 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.045 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 15 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 4 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 20 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 25 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.011 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 30 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 35 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 40 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.001 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 45 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 50 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 55 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 3 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 60 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 65 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.001 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 70 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.001 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 75 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 1 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.

Query: SELECT t1.Bo_Title , t1.Bo_Description , t1.Bo_Keyword , t1.Bo_Total , t1.Bo_Last , t1.Bo_Number , t1.Bo_Moderated , t1.Bo_Read_Perm , t1.Bo_Write_Perm , t1.Bo_Threads , t1.Bo_Sorter , t1.Bo_Posterid , t1.Bo_LastMain , t1.Bo_LastNumber , t2.U_Username , t3.B_Subject , t3.B_Icon , t3.B_Reged , t3.B_AnonName , t1.Bo_Image FROM w3t_Boards AS t1 LEFT JOIN w3t_Users AS t2 ON t1.Bo_Posterid = t2.U_Number LEFT JOIN w3t_Posts AS t3 ON t1.Bo_LastNumber = t3.B_Number WHERE t1.Bo_Cat = 100 AND (t1.Bo_Read_Perm LIKE '%-3-%' OR t1.Bo_Read_Perm LIKE '%-1-%' OR t1.Bo_Read_Perm LIKE '%-2-%') ORDER BY t1.Bo_Sorter
table type possible_keys key key_len ref rows Extra
t1 ref Cat_ndx Cat_ndx 4 const 2 where used; Using filesort
t2 eq_ref PRIMARY,indx3 PRIMARY 4 t1.Bo_Posterid 1
t3 eq_ref PRIMARY,w3t_Postsindex2 PRIMARY 4 t1.Bo_LastNumber 1
Query took a total of 0.002 seconds.


4x4Wire.com | Contact Us | Privacy statement


UBB.threads™ 6.4.1

Generated in 21.559 seconds in which 19.261 seconds were spent on a total of 24 queries. Zlib compression enabled.

Strange, all of those individual query times don't add up to the full mysql execution time. What you might try is during a slow period you can run the following from the sql command prompt:

SHOW PROCESSLIST

That will give you a list of the queries being run at that time and if any of them are locking the table. I'm guessing it could be related to the search which has some improvements coming in 6.5.

Hey Rick, I'll try that later on today. I was on our board at 6am today, there were 28 users, and it was dead slow ie 25 secs, then sped up to sub-second page generation. This is really frustrating. <img src="https://www.ubbcentral.com/boards/images/graemlins/frown.gif" alt="" />