I am not sure if this is due to the import or just a issue in general, when clicking on a board and retrieving all the posts it is slow to load. If I drop the attachments of the join and SELECT then it drops to < 0.5s on all my tests.
The attachments table is currently empty, I tried adding one attachment to see if that helped but nothing. There is about 393,731 Posts in 136,146 Topics in that board. So not that many, although a fair few.
SELECT
t.id_topic, t.num_replies, t.locked, t.num_views, t.num_likes, t.is_sticky, t.id_poll, t.id_previous_board,
COALESCE(lt.id_msg, lmr.id_msg, -1) + 1 AS new_from,
t.id_last_msg, t.approved, t.unapproved_posts, t.id_redirect_topic, t.id_first_msg,
ml.poster_time AS last_poster_time, ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
ml.poster_name AS last_member_name, ml.id_member AS last_id_member, ml.smileys_enabled AS last_smileys,
COALESCE(meml.real_name, ml.poster_name) AS last_display_name,
mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
mf.poster_name AS first_member_name, mf.id_member AS first_id_member, mf.smileys_enabled AS first_smileys,
COALESCE(memf.real_name, mf.poster_name) AS first_display_name
,meml.avatar,COALESCE(a.id_attach, 0) AS id_attach,a.filename,a.attachment_type,meml.email_address
FROM elkarte_topics AS t
INNER JOIN elkarte_messages AS ml ON (ml.id_msg = t.id_last_msg)
INNER JOIN elkarte_messages AS mf ON (mf.id_msg = t.id_first_msg)
LEFT JOIN elkarte_members AS meml ON (meml.id_member = ml.id_member)
LEFT JOIN elkarte_members AS memf ON (memf.id_member = mf.id_member)
LEFT JOIN elkarte_log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = 1)
LEFT JOIN elkarte_log_mark_read AS lmr ON (lmr.id_board = 8 AND lmr.id_member = 1)
LEFT JOIN elkarte_attachments AS a ON (a.id_member = ml.id_member AND a.id_member != 0)
WHERE t.id_board = 8
ORDER BY is_sticky DESC, t.id_last_msg DESC
LIMIT 0, 20
in .../sources/subs/MessageIndex.subs.php line 138, which took 16.95581889 seconds at 0.51222014 into request.
That does not seem right (yes captain obvious here)
Could you run that through explain, I wonder if an index is missing somewhere. Thanks for the report!
Here is what I'm seeing ... but this is just on a small test db I currently have up.
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|
1 | SIMPLE | t | ref | last_message, first_message, id_board, last_message_s... | last_message_sticky | 2 | const | 578 | Using where |
1 | SIMPLE | ml | eq_ref | PRIMARY | PRIMARY | 4 | elkartesql1.t.id_last_msg | 1 | |
1 | SIMPLE | meml | eq_ref | PRIMARY | PRIMARY | 3 | elkartesql1.ml.id_member | 1 | |
1 | SIMPLE | mf | eq_ref | PRIMARY | PRIMARY | 4 | elkartesql1.t.id_first_msg | 1 | |
1 | SIMPLE | memf | eq_ref | PRIMARY | PRIMARY | 3 | elkartesql1.mf.id_member | 1 | |
1 | SIMPLE | lt | eq_ref | PRIMARY,id_topic | PRIMARY | 6 | const,elkartesql1.t.id_topic | 1 | |
1 | SIMPLE | lmr | const | PRIMARY | PRIMARY | 5 | const,const | 1 | |
1 | SIMPLE | a | ref | id_member | id_member | 3 | elkartesql1.ml.id_member | 19 | Using where |
Sorry I had run it through explain, I forgot to put the results here. They are...
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ref last_message,first_message,id_board,last_message_s... id_board 2 const 118073 Using temporary; Using filesort
1 SIMPLE ml eq_ref PRIMARY PRIMARY 4 elkarte.t.id_last_msg 1
1 SIMPLE meml eq_ref PRIMARY PRIMARY 3 elkarte.ml.id_member 1
1 SIMPLE mf eq_ref PRIMARY PRIMARY 4 elkarte.t.id_first_msg 1
1 SIMPLE memf eq_ref PRIMARY PRIMARY 3 elkarte.mf.id_member 1
1 SIMPLE lt eq_ref PRIMARY,id_topic PRIMARY 6 const,elkarte.t.id_topic 1
1 SIMPLE lmr const PRIMARY PRIMARY 5 const,const 1
1 SIMPLE a ALL id_member NULL NULL NULL 1 Using where; Using join buffer (flat, BNL join)
It has an index on id_member it just ignores it.
Just to be sure it has these indexs on the attachment table
PRIMARY id_attach
UNIQUE id_member, id_attach
INDEX id_msg
INDEX attachment_type
INDEX id_thumb
postgre?
Correct, MySQL on this one. Well MariaDB.
I’m going to look at the my.cnf later and tweak that is it’s a default Debian install currently.
I should have noted that the explain I posted was from MariaDB (10.2) ... really strange that its ignoring that index.
It sees the index, for some reason it chooses to ignore it.
So changing the join_buffer_size from 256.000 KiB to 512KiB removed the slow query and it drops to 0.0006 seconds.
It still ignores the index though and has the following on a explain; Using where; Using join buffer (flat, BNL join)
Interestingly (to me) if I remove this from the SELECT
COALESCE(a.id_attach, 0) AS id_attach,a.filename,a.attachment_type,
then it will use the index and has this in the explain Using where; Using index
I tried all combinations of those variables but it wanted them all to use the index. I am running 10.3.27
Well that speed increase was short lived, it seems MySQL had cached my query so testing with that disabled means it jumps to 15seconds again.
It’s something related to the join query size I feel. I’ll see what more I can find. Although I think that query could be split out as it’s got quite a few joins.
Thank you for you effort on this ... I'm going to ping
@emanuele to see if he has any idea on this. In the meantime I need to build up a DB with lots more data so I can reproduce this behavior. I think my db will be busy tonight :D
Could there be a relationship to
https://www.elkarte.net/community/index.php?topic=5933.0
?
I worked out why it wasn't using the index if that helps. I think this is a very unique set of circumstances but if you have no attachments ( or very few ) then the index is never built as MySQL correctly doesn't think it needs to. The issue is when you are joining a large table it sorts through them and tries to find something and then falls over.
I populated the table with 10 empty records for the attachments and it uses the index the same as your's did @spuds.
That is really strange, almost seems like a bug in the db query optimizer. I can cause the index issue when I remove the AND a.id_member != 0 which is there to make the index query. I'm still building up a larger db for testing, forgot to run it last night so its running now so I can test and see if I can repo this on 10.2.
I don’t think it’s a bug in the optimiser, it makes logical sense in how it would do it.
The only optimisation you can do is a sub select really to pull those results back. I don’t think the Elkarte parser supports those though, it expect it will also only benefit when you don’t have the index, but that would need to be verified.
I’m happy to class this one as just a weird set of circumstances. It’s easy enough to mitigate by populating the table with some empty records or uploading some attachments.
I have not gotten mine to the point where it is using temporary and thus far I'm only in the 5 digits vs your 6 digit initial.
1.1.7 will support sub selects, that was part of that limit issue you helped debug. We could query the attach table and if empty force use avatars on index to false for that query.
Like you noted a bit of a corner case but that is what makes it interesting.