I've been paying attention to tuning my database settings after an upgrade (I'm now running "mysql Ver 15.1 Distrib 10.4.8-MariaDB"). Using tools like mysqltuner.pl, I see that my forum is performing a number of joins on tables without indexes - there's more than 100K after running for a few days. These tools commonly suggest increasing the join_buffer_size in this situation, but before I do that I'd prefer to understand what's going on. My database uses the default MyISAM engine for all tables as originally configured when I first installed SMF several years ago. So, a few questions:
- Looking at the sample captured query below, the smf_moderators table has primary keys, but no index that I can see. Why might this table (and any others) not have an index?
- Is this issue something that can be addressed by switching from MyISAM to InnoDB? I noticed that the mysql installer for SMF 2.1 defaults to MyISAM for a fresh installation, so I have to believe that the developers don't see a compelling reason for a complete switch,
- Is this really even an issue? The forum seems to be performing just fine.
# User@Host: maria[maria] @ localhost []
# Thread_id: 105490 Schema: smf QC_hit: No
# Query_time: 0.000805 Lock_time: 0.000119 Rows_sent: 0 Rows_examined: 22
# Rows_affected: 0 Bytes_sent: 147
# Full_scan: Yes Full_join: Yes Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0 Priority_queue: No
SET timestamp=1571068002;
SELECT b.id_board, bp.add_deny
FROM smf_board_permissions AS bp
INNER JOIN smf_boards AS b ON (b.id_profile = bp.id_profile)
LEFT JOIN smf_moderators AS mods ON (mods.id_board = b.id_board AND mods.id_member = 0)
WHERE bp.id_group IN (-1, 3)
AND bp.permission IN ('moderate_board')
AND (mods.id_member IS NOT NULL OR bp.id_group != 3) AND (FIND_IN_SET(-1, b.member_groups) != 0);
Thanks for any insights...
I would guess generally smf_moderators table is very small in most cases.
^^^ What he said.
Answers to each question:
1. A few points here. First, a primary key is always indexed in MySQL. So there is, in fact, an index on the table. Also note that a full table scan is sometimes the most efficient way to get at a table, for example, a very tiny table. Or, if you have to read an entire table; physical sequential reads are the quickest way to get the whole thing & an index might even force you to read it inefficiently, out of sequence.
I'm sure the optimizer looked at the table, found it to be tiny, and determined that the full scan made the most sense & ignored the index. It likely got the whole thing in one read.
2. MyISAM vs InnoDb won't make much of a difference in this example - they both honor the rules above.
Note that a fresh 2.1 install defaults to InnoDB if it is available:
https://github.com/SimpleMachines/SMF2.1/blob/8d19779a446b31de8c0effef453afbc144eeeafb/other/install.php#L1169
3. No.
Thanks for the replies. With respect to the default engine, sorry, it was the large upgrade script that I was looking at. For example, look at lines 41-50 of file upgrade_2-1_mysql.sql:
CREATE TABLE IF NOT EXISTS {$db_prefix}member_logins (
id_login INT(10) AUTO_INCREMENT,
id_member MEDIUMINT NOT NULL DEFAULT '0',
time INT(10) NOT NULL DEFAULT '0',
ip VARBINARY(16),
ip2 VARBINARY(16),
PRIMARY KEY id_login(id_login),
INDEX idx_id_member (id_member),
INDEX idx_time (time)
) ENGINE=MyISAM;
There are other examples in the same script.
That's the upgrade, not a fresh install. The upgrade does not do an InnoDB conversion for you.
Lainaus käyttäjältä: vbgamer45 - lokakuu 14, 2019, 01:36:10 IP
I would guess generally smf_moderators table is very small in most cases.
Even if it isn't, in virtually every case it'll be mostly or completely table scanned in practice.
Lainaus käyttäjältä: shawnb61 - lokakuu 15, 2019, 12:18:48 IP
That's the upgrade, not a fresh install. The upgrade does not do an InnoDB conversion for you.
I noted that it's the upgrade script. Why would the upgrade script create these tables using the MyISAM engine and not select the engine in the same way the fresh install does?
Almost everyone on 2.0 uses MyISAM, so it was logical for the upgrade to keep that presumption. Changing that out requires a complete overhaul of the upgrader, something no one ever wanted to touch.
Lainaus käyttäjältä: Arantor - lokakuu 15, 2019, 01:25:11 IP
Almost everyone on 2.0 uses MyISAM, so it was logical for the upgrade to keep that presumption. Changing that out requires a complete overhaul of the upgrader, something no one ever wanted to touch.
Got it - thanks!