title: How to speed up a highly active big data table (MySQL)?
body: <p>I'll begin to try and explain my problem and what I meant with the title.
  Currently I have got a table with around ~8 million rows.
  This table is highly active, what this means is there's constant updates, inserts and deletes.
  These are caused by users (it's like a collecting game). Meaning I also need to make sure the data is accurately displayed.</p>
  <p>I've looked so far into:</p>
  <ul>
  <li>indexing</li>
  <li>partitioning</li>
  <li>sharding</li>
  <li>mapreduce</li>
  <li>optimize</li>
  </ul>
  <p>I applied indexing, however I'm not sure if I applied this method correctly and it doesn't seem to help much more than I thought.</p>
  <p>As I said, my table is highly active, meaning that if I'd add partitioning to this table, it would mean there are going to be additional inserts/deletes and make this process way more complex than I can understand. I do not have that much experience with databases.</p>
  <p>Sharding this database is way too complex for me and I only have one service I can run this database on, so this option is a no-go.</p>
  <p>As for mapreduce, I am not entirely sure what this does, but as far as I understood, it mainly has to do more so with the code, than with the database.</p>
  <p>I applied optimize, but it didn't really seem to have too much effect neither as I experienced.</p>
  <p>I have tried to not use the * in SELECT statements, I made sure to get rid of most DISTINCT, COUNT and other functionalities of SQL alike, so that these wouldn't affect the speed of the database.
  However even after narrowing down the data in each table and specifically this table, it's currently slower than it was before this.</p>
  <p>This table consists of:</p>
  <pre><code>CREATE TABLE `claim` (
     `global_id` bigint NOT NULL AUTO_INCREMENT,
     `fk_user_id` bigint NOT NULL,
     `fk_series_id` smallint NOT NULL,
     `fk_character_id` smallint NOT NULL,
     `fk_image_id` int NOT NULL,
     `fk_gif_id` smallint DEFAULT NULL,
     `rarity` smallint NOT NULL,
     `emoji` varchar(31) DEFAULT NULL,
     PRIMARY KEY (`global_id`),
     UNIQUE KEY `global_id_UNIQUE` (`global_id`),
     KEY `fk_claim_character_id` (`fk_character_id`),
     KEY `fk_claim_image_id` (`fk_image_id`),
     KEY `fk_claim_series_id` (`fk_series_id`),
     KEY `fk_claim_user_id` (`fk_user_id`) /*!80000 INVISIBLE */,
     KEY `fk_claim_gif_id` (`fk_gif_id`) /*!80000 INVISIBLE */,
     KEY `fk_claim_rarity` (`rarity`) /*!80000 INVISIBLE */,
     KEY `fk_claim_emoji` (`emoji`),
     CONSTRAINT `fk_claim_character_id` FOREIGN KEY (`fk_character_id`) REFERENCES `character` (`character_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_claim_image_id` FOREIGN KEY (`fk_image_id`) REFERENCES `image` (`image_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_claim_series_id` FOREIGN KEY (`fk_series_id`) REFERENCES `series` (`series_id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_claim_user_id` FOREIGN KEY (`fk_user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
   ) ENGINE=InnoDB AUTO_INCREMENT=7622452 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  </code></pre>
  <p>Is there possibly another solution to speed up the database? If so, how? I'm currently at wits end and stuck on it. The database needs to respond preferably within 300ms.</p>
  <p>EXAMPLE SLOW QUERIES:</p>
  <pre><code>SELECT PK FROM &lt;table&gt; WHERE fk_user_id = ?;
  SELECT PK FROM &lt;table&gt; WHERE fk_user_id = ? GROUP BY fk_character_id HAVING MAX(fk_character_id) = 1;
  SELECT PK, fk_user_id, fk_character_id, etc, etc, etc FROM &lt;table&gt; WHERE fk_user_id = ? ORDER BY PK ASC LIMIT 0, 20
  </code></pre>