Stuart Wright
Well-known member
The following have been reported by my host Tim at Nimbus as slow queries.
I've included these here because I believe they are probably core Xenforo queries.
Can anything be done to speed them up please?
There are lots more, but I wanted to see whether this thread is welcome or not, first.
I've included these here because I believe they are probably core Xenforo queries.
Can anything be done to speed them up please?
# Query_time: 0.002588 Lock_time: 0.000043 Rows_sent: 235 Rows_examined: 521
use xfavforums;
SET timestamp=1384686878;
SELECT forum.*
,
NULL AS forum_read_date
FROM xf_forum AS forum
INNER JOIN xf_node AS node ON (node.node_id = forum.node_id)
WHERE forum.node_id IN (104, 283, 367, 82, 155, 232, 431, 43, 121, 39, 263, 107, 239, 259, 261, 313, 148, 126, 113, 373, 47, 114, 87, 332, 44, 130, 45, 127, 245, 147, 73, 164, 333, 209, 284, 487, 123, 381, 395, 137, 277, 129, 246, 59, 54, 172, 311, 180, 361, 152, 161, 183, 168, 460, 37, 55, 78, 420, 491, 493, 247, 427, 294, 384, 355, 387, 448, 349, 449, 457, 348, 393, 450, 451, 356, 452, 453, 454, 455, 306, 309, 304, 385, 205, 200, 207, 293, 423, 494, 206, 166, 143, 224, 267, 237, 254, 440, 441, 456, 258, 262, 442, 443, 444, 445, 446, 447, 392, 170, 391, 228, 271, 273, 292, 434, 424, 274, 96, 100, 388, 265, 374, 375, 419, 314, 315, 276, 327, 376, 280, 320, 323, 321, 322, 266, 302, 145, 192, 106, 462, 437, 438, 396, 403, 371, 489, 326, 470, 411, 397, 398, 399, 400, 401, 80, 240, 175, 189, 194, 61, 291, 410, 407, 465, 430, 467, 474, 303, 119, 117, 62, 198, 185, 338, 153, 241, 330, 476, 343, 85, 243, 154, 358, 51, 329, 379, 380, 199, 142, 353, 99, 340, 402, 341, 492, 339, 394, 409, 344, 268, 390, 50, 298, 357, 203, 105, 79, 414, 211, 234, 481, 346, 212, 484, 188, 162, 463, 279, 482, 436, 38, 151, 176, 286, 179, 256, 301, 432, 305, 461, 354, 464, 251, 264, 52);
# Query_time: 0.003870 Lock_time: 0.000037 Rows_sent: 237 Rows_examined: 523
SET timestamp=1384686879;
SELECT forum.*
,
IF(forum_read.forum_read_date > 1382094879, forum_read.forum_read_date, 1382094879) AS forum_read_date
FROM xf_forum AS forum
INNER JOIN xf_node AS node ON (node.node_id = forum.node_id)
LEFT JOIN xf_forum_read AS forum_read ON
(forum_read.node_id = forum.node_id
AND forum_read.user_id = 342880)
WHERE forum.node_id IN (104, 283, 367, 82, 155, 232, 431, 43, 121, 39, 263, 107, 239, 259, 261, 313, 148, 126, 113, 373, 47, 114, 87, 332, 44, 130, 45, 127, 245, 147, 73, 164, 333, 209, 284, 487, 123, 381, 395, 137, 277, 129, 246, 59, 54, 172, 311, 180, 361, 152, 161, 183, 168, 460, 37, 55, 78, 420, 491, 493, 247, 427, 294, 384, 355, 387, 448, 349, 449, 457, 348, 393, 450, 451, 356, 452, 453, 454, 455, 306, 309, 304, 385, 205, 200, 207, 293, 423, 494, 206, 166, 143, 224, 267, 237, 254, 440, 441, 456, 258, 262, 442, 443, 444, 445, 446, 447, 392, 170, 391, 228, 271, 273, 292, 434, 424, 274, 96, 100, 388, 265, 374, 375, 419, 314, 315, 276, 327, 376, 280, 320, 323, 321, 322, 266, 302, 145, 192, 106, 462, 437, 438, 396, 403, 371, 489, 326, 470, 411, 397, 398, 399, 400, 401, 80, 240, 175, 189, 194, 61, 291, 410, 407, 465, 430, 467, 474, 303, 119, 117, 62, 198, 185, 338, 153, 241, 330, 476, 343, 85, 243, 154, 358, 51, 329, 379, 380, 199, 142, 353, 99, 340, 402, 341, 492, 339, 394, 409, 344, 252, 268, 390, 50, 298, 357, 203, 105, 79, 414, 211, 234, 481, 346, 212, 484, 188, 162, 463, 279, 482, 436, 38, 151, 176, 286, 179, 256, 301, 432, 305, 461, 354, 464, 251, 264, 52, 275);
I added indexes to the site_title and addon_id columns in the hope that these would help. (My understanding is that any columns being used in matches or sorts in queries should be considered for having an index on them)# Query_time: 0.104526 Lock_time: 0.051345 Rows_sent: 6 Rows_examined: 12
SET timestamp=1384686881;
SELECT bb_code_media_site.*
FROM xf_bb_code_media_site AS bb_code_media_site
LEFT JOIN xf_addon AS addon ON (addon.addon_id = bb_code_media_site.addon_id)
WHERE 1=1
ORDER BY bb_code_media_site.site_title;
There are lots more, but I wanted to see whether this thread is welcome or not, first.