0

We have a production project running on MySQL with following scheme [There is obviously many more columns but I omitted irrelevant columns to simplify the question]

# Has approximately 9 million rows
CREATE TABLE users
(
    id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

# A few hundreds
CREATE TABLE items
(
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(200) NOT NULL,
    category TINYINT NOT NULL
);

# More than 22 million rows
CREATE TABLE user_items
(
    id           BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    item_id      BIGINT NOT NULL,
    date_created DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    is_active    TINYINT DEFAULT 0 NOT NULL,
    
    CONSTRAINT user_items_items_id_fk FOREIGN KEY (item_id) REFERENCES items (ID),
    CONSTRAINT user_items_users_id_fk FOREIGN KEY (user_id) REFERENCES users (ID)
);

A user can own multiple items, own multiple instances of the same item but can have only one active item per category. There are only 4 categories but we can have more in the future

We have multiple queries that looks like this. You can say it is very common for many features of our system

SELECT `users`.*, user_items.`item_id` AS `active_item`
FROM `users` 
    LEFT JOIN `user_items` ON `users`.`id` = `user_items`.`user_id`
    LEFT JOIN `items` ON `user_items`.`item_id` = `items`.`id`
WHERE `users`.`id` = @userId AND `user_items`.`is_active` AND `items`.`category` = @category

Recently, all queries of this kind are slowing down. We are starting to feel the hit.

We run EXPLAIN on some quires and the result shows that it is user_items table.

|id |select_type       |table            |partitions|type  |possible_keys                          |key         |key_len|ref           |rows|filtered|Extra                          |
|---|------------------|-----------------|----------|------|---------------------------------------|------------|-------|--------------|----|--------|-------------------------------|
|1  |PRIMARY           |players          |          |const |PRIMARY                                |PRIMARY     |4      |const         |1   |100     |Using temporary; Using filesort|
|1  |PRIMARY           |users            |          |const |PRIMARY                                |PRIMARY     |4      |const         |1   |100     |                               |
|1  |PRIMARY           |rankings         |          |const |PRIMARY                                |PRIMARY     |4      |const         |1   |100     |                               |
|1  |PRIMARY           |tournaments      |          |const |PRIMARY                                |PRIMARY     |4      |const         |1   |100     |                               |
|1  |PRIMARY           |profiles         |          |const |PRIMARY                                |PRIMARY     |4      |const         |1   |100     |                               |
|1  |PRIMARY           |user_achievements|          |ref   |PRIMARY,achievement_id                 |PRIMARY     |4      |const         |6   |100     |                               |
|1  |PRIMARY           |achievements     |          |eq_ref|PRIMARY                                |PRIMARY     |92     |achievement_id|1   |100     |Using where                    |
|1  |PRIMARY           |user_trophies    |          |ref   |trophies_fk_idx,users_fk_idx           |users_fk_idx|4      |const         |1   |100     |                               |
|1  |PRIMARY           |trophies         |          |eq_ref|PRIMARY                                |PRIMARY     |92     |trophy_id     |1   |100     |Using where                    |
|4  |DEPENDENT SUBQUERY|user_trophies    |          |ref   |users_fk_idx                           |users_fk_idx|4      |const         |14  |100     |Using index                    |
|3  |SUBQUERY          |user_items       |          |ref   |is_active,FK_user_items_item_id,user_id|user_id     |4      |const         |2141|2.72    |Using where                    |
|3  |SUBQUERY          |items            |          |eq_ref|PRIMARY,category_id                    |PRIMARY     |8      |item_id       |1   |67.4    |Using where                    |
|2  |DEPENDENT SUBQUERY|                 |          |      |                                       |            |       |              |    |        |no matching row in const table |

We thought of adding an index on user_items.is_active but that is a boolean value and data is very skewed, as a user can have hundreds of items but only few are active. I think the index will do more harm then good.

I was wandering if there are any alternatives to improve performance of such queries

5
  • I hope you are aware of the fact that an index can contain more than one column. Particularly, one on (user_id, is_active, item_id) might help. Also, what's the deal with the LEFT JOINs that aren't?
    – mustaccio
    May 5, 2023 at 17:12
  • I wanted to suggest to our tech lead using a composite index but I wanted to confirm if a key on boolean column is a bad idea or not. Regarding the query, I just wanted to write a simple one to get the idea across. All our queries are generated by EF ORM, some are monolithic and unreadable. May 5, 2023 at 17:25
  • Other idea I had is having a separate table for active_user_items but that has its own set of technical challenges. So I'm seeking advice here May 5, 2023 at 17:27
  • @AliAlBarrak the explain plan isn't of the query provided . Please post correct execution plan. user_items.is_active shouldn't this be equal to something ? user_items.is_active = 1. May 5, 2023 at 17:44
  • As I mentioned, the query shown is just a sample for simplicity, real queries are auto generated by the framework we are using. They are quite unreadable. Query for the EXPLAIN above is like this May 5, 2023 at 17:58

2 Answers 2

0

First you might consider removing the users table from this query:

SELECT `users`.*, user_items.`item_id` AS `active_item`
FROM `users` 
    LEFT JOIN `user_items` ON `users`.`id` = `user_items`.`user_id`
    LEFT JOIN `items` ON `user_items`.`item_id` = `items`.`id`
WHERE `users`.`id` = @userId 
AND `user_items`.`is_active` 
AND `items`.`category` = @category

If you fetch a lot of rows (for example all items owned by a user) then the columns in user will be duplicated in each row. Even if you fetch only one item, do you really need to fetch the row from the users table? I guess you probably already have all the info it contains available in your code somewhere else, for example when the user row was selected at session verification.

In tables with a few INT columns, SELECT * is just as fast as selecting only the columns you need, and you shouldn't worry about it. However if the users table contains large TEXT columns like their bio, forum signatures, or multiple URL columns, selecting users.* can end up moving a lot of data and clog up your database bandwidth for dubious utility if you're not actually using the information.

--

Table items is small so it can be cached in RAM in the client is possible. Then there is no need to query it.

--

Data integrity: your current structure does not allow an easy way for a database constraint to check that only one item can be active in a category. In addition the category column is in the items table so it cannot be indexed in the user_items table, which means the database will have to scan all the active item rows to find the active item in a category. This may or may not be a problem depending on row counts.

Suggestion: change the primary key of items to (category_id,item_id) and reference that from users_items, thus duplicating the category into table users_items in a way that ensures integrity. Also add unique constraint on item_id, to avoid rewriting all your code using item_id to add category_id on each select everywhere.

I see category_id is a tinyint, which hints of trying to save some bytes, in this vein item_id doesn't need to be a bigint, changing to a short will save a bit of space in users_items and associated indices.

OK. Now that category_id is duplicated into users_items lets do that constraint. Unlike postgres, mysql doesn't do conditional indices, so I suggest the following:

  • Set is_active to NULL for all non-active items, and TRUE for the active item
  • Create unique index (user_id,category_id,is_active)

This exploits the fact that MySQL allows "duplicate" NULL values in a UNIQUE index constraint. This is semantically correct because "NULL" sort of means "unknown" so it does make sense to allow several rows with unknown values even in an unique constraint. Also note NULL is not equal to NULL. However TRUE IS NOT NULL, so there can be only one row per (user_id,category_id) with is_active=TRUE. So now your constraint will be enforced.

This index also allows fast search of active items per user and category, which is what you wanted.

It is possible to use (user_id,is_active,category_id) instead. This may have better cache locality as all the index pages having is_active=TRUE will be clustered together in index order.

Now I would recommend to use (user_id,item_id,id) as primary key for users_items for performance reasons. That's because InnoDB clusters table by primary key so this will put all the row for one user's items close together in cache, which is a good thing as your cache will fill with the rows of users currently playing the game, and not with useless rows from users not playing the game at the moment. This has extra complication of having to generate a row id because (user_id,item_id) is not unique. I was about to suggest adding a count column to coalesce multiple items owned by the same user into a single row but if you did it that way it probably means users_items also has bonuses and other modifiers for each instance of the item, so that wouldn't work.

Note to use (user_id,item_id,id) as primary key, then item_id must be unique in the items table, two items from different categories can't have the same item_id. So the items table kinda has two primary keys, with one official.

An index on is_active alone would most likely be useless due to lack of selectivity.

An index on (user_id, item_id, is_active) doesn't have category, so meh.

An index on (user_id, item_id) is included for free in the primary key suggested above, so no need to duplicate it.

Another option would be to put the active items in one table, and the inactive items in another table. Drawbacks: it's annoying, you have to move rows. Advantages: if your users are like the usual RPG players they have 4 active items and 999999 pieces of junk, so your active table will be tiny, well cached and fast. Also you can create more indices for your active items, without fattening them up with the inactive ones.

2
  • Thanks for your help. That's a lot to digest at once but I'll try going over it. As for SELECT * that was just a sample. Our queries are generated by ORM with projection, selecting only what is needed. Regarding caching the users. We have multiple servers. Some are UDP and user info are in memory for those but we have a TCP server and all request to it are stateless. Everything else you suggested seems to make sense to me. Like caching items in memory. Changes to keys and indices also makes sense. I'll consider everything you said and think this issue over May 5, 2023 at 18:18
  • Thanks, i hope it helps ;)
    – bobflux
    May 5, 2023 at 19:11
0

First, you can change outer join to inner join because there are NFC conditions for both outer tables:

 SELECT `users`.*, user_items.`item_id` AS `active_item`
 FROM `users` ,`user_items`, `items`
 WHERE `users`.`id` = `user_items`.`user_id`
    AND `user_items`.`item_id` = `items`.`id`
    AND `users`.`id` = @userId 
    AND `user_items`.`is_active` 
    AND `items`.`category` = @category

Secondly, following indexes should be helpful to get a optimized query plan.

CREATE INDEX PAWSQL_IDX1525474463 ON users(ID);
-- 1. When table users serves as a DRIVE table in the join planning, PAWSQL_IDX1525474463 can be used to do a index LOOKUP with condition(users.id = 648).
CREATE INDEX PAWSQL_IDX1019552786 ON items(CATEGORY,ID);
-- 1. When table items serves as a DRIVEN table in the join planning, PAWSQL_IDX1019552786 can be used to do a COVERING index LOOKUP with condition(user_items.item_id = items.id and items.category = 655).
-- 2. When table items serves as a DRIVE table in the join planning, PAWSQL_IDX1019552786 can be used to do a COVERING index LOOKUP with condition(items.category = 655).
CREATE INDEX PAWSQL_IDX0289438459 ON user_items(ITEM_ID,USER_ID);
-- 1. When table user_items serves as a DRIVEN table in the join planning, PAWSQL_IDX0289438459 can be used to do a COVERING index LOOKUP with condition(users.id = user_items.user_id and user_items.item_id = items.id).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.