title: Reduce index growth rate in MySQL
body: <p>On my service <a href="https://simibat.ru/fftracker" rel="nofollow noreferrer">https://simibat.ru/fftracker</a> I have some many-to-many (junction) tables, for example for character achievements, which consist of 2 columns with foreign key (one of them is also a composite primary key) and a 3rd column with dates or something else relevant. In the example of achievements table, it has 36+ million of rows, approximately 1 GB of total size with index a bit lower than 500 MB. Nothing special, I know. What concerns me is the rate of index growth.</p>
  
  <p>In about a week index gets at least ~400 extra MBs, for ~10 MBs (at most) of actual new data. After table optimization those 400 MBs turn into ~5 MBs. It's not that big of an issue to run optimization every week, but I am curious if there is a way to reduce such significant (and pointless) growth rate, since this seems to be a proper approach.</p>
  
  <p>I know, I can try simply dropping the foreign key for achievements ID (since it's already used in composite index which is used in all of my queries), but this feels like an overcomplicated migration of logic to PHP, instead of leveraging native functions.</p>
  
  <p>So, any idea how I can approach this?</p>
  
  <p><strong>Update</strong>
  Another thing to note: I am running INSERT...ON DUPLICATE KEY UPDATE. Can it be, tat this updates the index, which results in it growing?</p>
  
  <p><strong>CREATES</strong> of the tables as requested. The table I have concern with is ff__character_achievement (junctions), the rest are mainly for reference (just in case).</p>
  
  <pre><code>   CREATE TABLE `ff__character_achievement` (
       `characterid` int(10) unsigned NOT NULL,
       `achievementid` smallint(5) unsigned NOT NULL,
       `time` date NOT NULL,
       PRIMARY KEY (`characterid`,`achievementid`),
       KEY `ach` (`achievementid`) USING BTREE,
       CONSTRAINT `char_ach_ach` FOREIGN KEY (`achievementid`) REFERENCES `ff__achievement` (`achievementid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `char_ach_char` FOREIGN KEY (`characterid`) REFERENCES `ff__character` (`characterid`) ON DELETE CASCADE ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  
  CREATE TABLE `ff__achievement` (
       `achievementid` smallint(5) unsigned NOT NULL,
       `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
       `category` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `subcategory` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `icon` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `howto` text COLLATE utf8mb4_unicode_ci,
       `points` tinyint(3) unsigned DEFAULT NULL,
       `title` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `item` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `itemicon` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       `itemid` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
       PRIMARY KEY (`achievementid`),
       KEY `name` (`name`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  
      CREATE TABLE `ff__character` (
       `characterid` int(10) unsigned NOT NULL,
       `userid` int(10) unsigned DEFAULT NULL,
       `serverid` tinyint(2) unsigned NOT NULL,
       `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
       `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       `freecompanyid` bigint(20) unsigned DEFAULT NULL,
       `biography` text COLLATE utf8mb4_unicode_ci,
       `titleid` smallint(5) unsigned DEFAULT NULL,
       `bio_avatar` varchar(66) COLLATE utf8mb4_unicode_ci NOT NULL,
       `clanid` tinyint(2) unsigned NOT NULL,
       `genderid` tinyint(1) unsigned NOT NULL,
       `namedayid` smallint(3) unsigned NOT NULL,
       `guardianid` tinyint(2) unsigned NOT NULL,
       `cityid` tinyint(1) unsigned NOT NULL,
       `fc_rankidprev` tinyint(2) unsigned DEFAULT NULL,
       `fc_rankid` tinyint(2) unsigned DEFAULT NULL,
       `fc_ranklvlupd` timestamp NULL DEFAULT NULL,
       `fc_nextprom` text COLLATE utf8mb4_unicode_ci,
       `gcrankid` tinyint(2) unsigned DEFAULT NULL,
       `Alchemist` tinyint(3) unsigned DEFAULT NULL,
       `Armorer` tinyint(3) unsigned DEFAULT NULL,
       `Astrologian` tinyint(3) unsigned DEFAULT NULL,
       `Bard` tinyint(3) unsigned DEFAULT NULL,
       `BlackMage` tinyint(3) unsigned DEFAULT NULL,
       `Blacksmith` tinyint(3) unsigned DEFAULT NULL,
       `Botanist` tinyint(3) unsigned DEFAULT NULL,
       `Carpenter` tinyint(3) unsigned DEFAULT NULL,
       `Culinarian` tinyint(3) unsigned DEFAULT NULL,
       `DarkKnight` tinyint(3) unsigned DEFAULT NULL,
       `Dragoon` tinyint(3) unsigned DEFAULT NULL,
       `Fisher` tinyint(3) unsigned DEFAULT NULL,
       `Goldsmith` tinyint(3) unsigned DEFAULT NULL,
       `Leatherworker` tinyint(3) unsigned DEFAULT NULL,
       `Machinist` tinyint(3) unsigned DEFAULT NULL,
       `Miner` tinyint(3) unsigned DEFAULT NULL,
       `Monk` tinyint(3) unsigned DEFAULT NULL,
       `Ninja` tinyint(3) unsigned DEFAULT NULL,
       `Paladin` tinyint(3) unsigned DEFAULT NULL,
       `RedMage` tinyint(3) unsigned DEFAULT NULL,
       `Samurai` tinyint(3) unsigned DEFAULT NULL,
       `Scholar` tinyint(3) unsigned DEFAULT NULL,
       `Summoner` tinyint(3) unsigned DEFAULT NULL,
       `Warrior` tinyint(3) unsigned DEFAULT NULL,
       `Weaver` tinyint(3) unsigned DEFAULT NULL,
       `WhiteMage` tinyint(3) unsigned DEFAULT NULL,
       PRIMARY KEY (`characterid`,`name`) USING BTREE,
       KEY `userid` (`userid`),
       KEY `char_fc` (`freecompanyid`) USING BTREE,
       KEY `genderid` (`genderid`),
       KEY `clanid` (`clanid`),
       KEY `guardianid` (`guardianid`),
       KEY `namedayid` (`namedayid`),
       KEY `cityid` (`cityid`),
       KEY `serverid` (`serverid`),
       KEY `gcrankid` (`gcrankid`),
       KEY `titleid` (`titleid`),
       KEY `fc_rankid` (`fc_rankid`),
       KEY `fc_rankidprev` (`fc_rankidprev`),
       CONSTRAINT `character_fcid` FOREIGN KEY (`freecompanyid`) REFERENCES `ff__freecompany` (`freecompanyid`) ON DELETE SET NULL ON UPDATE CASCADE,
       CONSTRAINT `cityid` FOREIGN KEY (`cityid`) REFERENCES `ff__city` (`cityid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `clanid` FOREIGN KEY (`clanid`) REFERENCES `ff__clan` (`clanid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `fc_rankid` FOREIGN KEY (`fc_rankid`) REFERENCES `ff__freecompany_rank` (`rankid`) ON DELETE SET NULL ON UPDATE SET NULL,
       CONSTRAINT `fc_rankidprev` FOREIGN KEY (`fc_rankidprev`) REFERENCES `ff__freecompany_rank` (`rankid`) ON DELETE SET NULL ON UPDATE SET NULL,
       CONSTRAINT `gcrankid` FOREIGN KEY (`gcrankid`) REFERENCES `ff__grandcompany_rank` (`gcrankid`) ON DELETE SET NULL ON UPDATE SET NULL,
       CONSTRAINT `genderid` FOREIGN KEY (`genderid`) REFERENCES `usersys__gender` (`genderid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `guardianid` FOREIGN KEY (`guardianid`) REFERENCES `ff__guardian` (`guardianid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `namedayid` FOREIGN KEY (`namedayid`) REFERENCES `ff__nameday` (`namedayid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `serverid` FOREIGN KEY (`serverid`) REFERENCES `ff__server` (`serverid`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `titleid` FOREIGN KEY (`titleid`) REFERENCES `ff__achievement` (`achievementid`) ON DELETE SET NULL ON UPDATE SET NULL,
       CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `usersys__logins` (`userid`) ON DELETE SET NULL ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT
  </code></pre>