XF 1.2 Tackling queries with some missing indexes

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?

# 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);

# 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;
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)

There are lots more, but I wanted to see whether this thread is welcome or not, first.
 
Indeed, the first 2 don't seem to be slow. The last one looks slightly slow (0.1s), but it's not a data issue - if it's slow, it's slow because of a knock on effect or the data needing to be read from the disk. It only examined 12 rows, so indexing is going to do very little.
 
Ok, Tim has corrected me. They are queries with some missing indexes and I have edited the thread title to reflect that.

Presumably in a lot of cases, adding indexes will improve query time, even if it's only by a small amount. It all adds up, right?
I have been reading up on this.
If there is an index composed of two columns, maybe to create a unique combination for a primary key, isn't it still worth having separate indexes on the individual columns if they are being queried individually? I read that generally having individual column indexes is faster.
I also read that indexes on columns used in the order by clause can improve the speed.
It's been over two decades since I did my SQL training at Oracle in Reading, so I'm probably a bit out of touch.
 
If there is an index composed of two columns, maybe to create a unique combination for a primary key, isn't it still worth having separate indexes on the individual columns if they are being queried individually? I read that generally having individual column indexes is faster.
In the 3 examples given, all of your indexes used for joins or filters are single column indexes, and in all but one case they are they primary index on the table.

I also read that indexes on columns used in the order by clause can improve the speed.
When you have > 500 records being returned, then perhaps. But rarely and certainly not in your scenario of only single or double digit records.
 
I don't think it will make sense to add an index just because you "can", your above queries are really fast, there is nothing to complain about.

Maybe you want to ask Tim what exactly he complains about?
 
Top Bottom