Tracking by IP seems excessively slow, query running for > 250 seconds
?action=profile;area=tracking;sa=ip;searchip=***;u=38590
SELECT COUNT(*) AS message_count FROM yabbse_messages AS m
INNER JOIN yabbse_boards AS b ON (b.id_board = m.id_board)
WHERE 1=1 AND m.poster_ip >= unhex('***') and m.poster_ip <= unhex('***')
|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* ||
|| 1 || SIMPLE || b || index || id_board || id_board || 2 || || 38 || Using index ||
|| 1 || SIMPLE || m || ref || ID_BOARD || ID_BOARD || 2 || rawk_testforum.b.id_board || 214766 || Using where ||
Which mysql/mariadb version you use?
Think it is still on 5.6 for legacy reasons, I'll have a look when I'm at a computr next and update it to the latest supported ver and see what indexes / changes are needed.
smf 2.1 use some features (cte) only when a rdbms avaible with the needed feature set,
in case of mysql you need 8.0.
but this wouldn't change you tracking ip issue.
In attempting to test this, I keep getting white pages/memory overflows. Must dig deeper.
I changed the above query to SELECT COUNT(m.id_msg) AS message_count and now it's down from 5 seconds to one.
count(*) should be faster as count(column)
maybe the execution plane did change by this...
COUNT(*) was only as fast as COUNT(columname) in MySQL on MyISAM tables; this is not true for InnoDB tables (or at least never used to be true) and it would instead do a table scan since unlike MyISAM tables, the number of rows isn't pre-stored.
Using COUNT(table-pk) is the fastest way I know of doing it on InnoDB, which is what everyone should be using anyway since MyISAM tables are rubbish.
The manuel say something different: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count
As usual, you're talking about the latest version which I assure you continues to be the minority.
Considering that Ubuntu's latest LTS (18.04) ships with 5.7 and I guarantee you people will continue to use that on the server for the next 5-10 years (as it is a 10 year LTS), that's possibly a more realistic benchmark of what to optimise for?
If I really wanted to do scale, I'd look at something like Amazon Aurora, which currently also runs 5.6/5.7 depending on your version.
You find the same manuel statement on the older version,
so nothing wrong here to link to the newest one.
If only it actually behaved according to the manual, then, as opposed to the point above where it demonstrably behaves better, and has a known basis for doing so that is not at all new.
My fix for this: https://github.com/SimpleMachines/SMF2.1/pull/5489