Advertisement:

Author Topic: JOINs Without Indexes  (Read 392 times)

Offline sah62

  • Semi-Newbie
  • *
  • Posts: 93
JOINs Without Indexes
« on: October 14, 2019, 01:34:01 PM »
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.

Code: [Select]
# 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...

Online vbgamer45

  • Customizer
  • SMF Super Hero
  • *
  • Posts: 21,627
    • smfhacks on Facebook
    • VBGAMER45 on GitHub
    • @createaforum on Twitter
    • SMF For Free
Re: JOINs Without Indexes
« Reply #1 on: October 14, 2019, 01:36:10 PM »
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

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,522
    • sbulen on GitHub
Re: JOINs Without Indexes
« Reply #2 on: October 14, 2019, 02:18:33 PM »
^^^  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

Offline sah62

  • Semi-Newbie
  • *
  • Posts: 93
Re: JOINs Without Indexes
« Reply #3 on: October 15, 2019, 11:10:00 AM »
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:

Code: [Select]
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.

Offline shawnb61

  • Developer
  • SMF Hero
  • *
  • Posts: 1,522
    • sbulen on GitHub
Re: JOINs Without Indexes
« Reply #4 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. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,868
    • StoryBB/StoryBB on GitHub
Re: JOINs Without Indexes
« Reply #5 on: October 15, 2019, 12:28:44 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline sah62

  • Semi-Newbie
  • *
  • Posts: 93
Re: JOINs Without Indexes
« Reply #6 on: October 15, 2019, 01:17:46 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?

Offline Arantor

  • Resident Overthinker
  • SMF Friend
  • SMF Legend
  • *
  • Posts: 71,868
    • StoryBB/StoryBB on GitHub
Re: JOINs Without Indexes
« Reply #7 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.
Don’t try to tell me that some power can corrupt a person. You haven’t had enough to know what it’s like.

No good deed goes unpunished / No act of charity goes unresented.

Offline sah62

  • Semi-Newbie
  • *
  • Posts: 93
Re: JOINs Without Indexes
« Reply #8 on: October 15, 2019, 01:26:42 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!