News:

Wondering if this will always be free?  See why free is better.

Main Menu

JOINs Without Indexes

Started by sah62, October 14, 2019, 01:34:01 PM

Previous topic - Next topic

sah62

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...

vbgamer45

I would guess generally smf_moderators  table is very small in most cases.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

shawnb61

^^^  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.

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

sah62

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.

shawnb61

That's the upgrade, not a fresh install.  The upgrade does not do an InnoDB conversion for you. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Arantor

Quote from: vbgamer45 on October 14, 2019, 01:36:10 PM
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.

sah62

Quote from: shawnb61 on October 15, 2019, 12:18:48 PM
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?

Arantor

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.

sah62

Quote from: Arantor on October 15, 2019, 01:25:11 PM
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!

Advertisement: