How to speed up a highly active big data table (MySQL)?

Question

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.

I've looked so far into:

• indexing
• partitioning
• sharding
• mapreduce
• optimize

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.

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.

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.

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.

I applied optimize, but it didn't really seem to have too much effect neither as I experienced.

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.

This table consists of:

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

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.

EXAMPLE SLOW QUERIES:

SELECT PK FROM <table> WHERE fk_user_id = ?;
SELECT PK FROM <table> 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 <table> WHERE fk_user_id = ? ORDER BY PK ASC LIMIT 0, 20

Answer 1

Redundant

PRIMARY KEY (`global_id`),
UNIQUE KEY `global_id_UNIQUE` (`global_id`),

A PRIMARY KEY, in MySQL, is a UNIQUE KEY. So the UNIQUE KEY is redundant, wastes disk space, and slows down INSERT.

Need VISIBLE index starting with user_id for Q1 and Q2

Replace this

   KEY `fk_claim_user_id` (`fk_user_id`) /*!80000 INVISIBLE */,

with

INDEX(fk_user_id, fk_character_id)

in that order -- this will help with your first 2 queries.

Query 3

The 3rd query may still need (in the given order)

INDEX(fk_user_id, global_id)

If you need some of the DISTINCTs/COUNTs, let's see them. Changing indexes may help.

Strange query

As for

SELECT PK FROM <table> WHERE fk_user_id = ?;

Why would you just want the PK? Is global_id useful by itself? Or is it useful only for looking up something else? If the latter, let's see it; it is often more practical to optimize a single, complex, query than two queries that are artificially split.

Tuning

How much RAM is available to MySQL? What is the value of innodb_buffer_pool_size? 30s for 50K rows -- sounds like being I/O-bound. Maybe that setting is too low.

In some cases, DISTINCT speeds up a query -- if for no other reason that less data is shoveled back to the client.

Redesign PK

Based on the names "claim" and "user_id" and the test for "user_id" in all 3 queries, I deduce that you are frequently looking up stuff for a single "user"? What, if anything, is global_id needed for outside this table?

If you need need global_id elsewhere or nothing else could be used for uniqueness, do

PRIMARY KEY(user_id, global_id), -- for locality of reference
INDEX(global_id)  -- to keep AUTO_INCREMENT happy

If (user_id, xx) is known to be unique (for some column(s) xx), toss global_id and change to

PRIMARY KEY(user_id, xx) 

In either case, these go away:

PRIMARY KEY (`global_id`),
UNIQUE KEY `global_id_UNIQUE` (`global_id`),
KEY `fk_claim_user_id` (`fk_user_id`) /*!80000 INVISIBLE */,

InnoDB stores the data in PK order. By having the PK start with user_id, all the rows for one user are "adjacent" on the disk, thereby more readily cached in RAM (in the buffer_pool).

Given a user with 100 claims, I am restructuring the table so that the data is found in a couple of consecutive blocks (16KB unit of storage by InnoDB) instead of upwards of 100 scattered blocks.