MySQL Leaderboard query optimization-CodePudding

I'm currently making a leaderboard where I'm using the specific code to go:


public long checkPosition(String table,String column, UUID uuid){
        DataAPI dataAPI = Core.getInstance().getDataAPI();

        String index = table   "_index_"   column;


        //SELECT id, name, score, FIND_IN_SET( score, (SELECT GROUP_CONCAT(score ORDER BY score DESC ) FROM scores )) AS rank FROM scores WHERE name =  'Assem'
        //SELECT 1   COUNT(*) AS rank FROM table WHERE " column " > (SELECT " column " FROM " table " WHERE uuid='" uuid.toString() "')

        int level = 300;

        String query = "SELECT 1   COUNT(*) AS rank FROM " table " FORCE INDEX(" index ") WHERE " column " > (SELECT `" column "` FROM week_statistics_users FORCE INDEX(week_statistics_users_index_uuid) WHERE `uuid`='" uuid.toString() "');";

        try (Connection connection = dataAPI.getConnection()
             ;PreparedStatement statement = connection.prepareStatement(query)){
            ResultSet resultSet;
            resultSet = statement.executeQuery();
            resultSet.next();
            return (long) resultSet.getObject(1);
        } catch (Exception ex){
            ex.printStackTrace();
        }
        return -1;
    }

You can see I am trying to force an index usage, I am trying to optimize that query, in a table of 1M user its taking 2 seconds - per query.

I don't know exactly why its taking all that, I can tell you the index is multi column, always the uuid, and the column string, all of the colums are BIGINTs, is there anyway I can optimize that query? am I making any mistake with the index? maybe I am not using it correctly, or maybe the issue is in the SELECT COUNT(*).

MariaDB [s2_goliath]> EXPLAIN SELECT 1   COUNT(*) AS rank FROM lifetime_statistics_users WHERE kills > (SELECT kills FROM week_statistics_users WHERE uuid='2bd9d043-8187-444c-8536-ebd843885ad2');
 ------ ------------- --------------------------- ------- --------------------------------------------------------------------------------- --------------------------------------- --------- ------- -------- -------------------------- 
| id   | select_type | table                     | type  | possible_keys                                                                   | key                                   | key_len | ref   | rows   | Extra                    |
 ------ ------------- --------------------------- ------- --------------------------------------------------------------------------------- --------------------------------------- --------- ------- -------- -------------------------- 
|    1 | PRIMARY     | lifetime_statistics_users | range | lifetime_statistics_users_index_kills,lifetime_statistics_users_index_killsuuid | lifetime_statistics_users_index_kills | 9       | NULL  | 118320 | Using where; Using index |
|    2 | SUBQUERY    | week_statistics_users     | const | PRIMARY,week_statistics_users_index_uuid                                        | PRIMARY                               | 402     | const | 1      |                          |
 ------ ------------- --------------------------- ------- --------------------------------------------------------------------------------- --------------------------------------- --------- ------- -------- -------------------------- 
2 rows in set (0.000 sec)

Update:

SHOW TABLE STATUS:

| Name                      | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary

| month_statistics_users    | InnoDB |      10 | Dynamic    | 248956 |            200 |    50020352 |               0 |    392118272 |   6291456 |           NULL | 2022-07-30 17:28:22 | 2022-07-31 16:53:50 | NULL       | utf8mb4_general_ci |     NULL |                |         |                0 | N        

```

SHOW CREATE TABLE:
```java

MariaDB [s2_goliath]> SHOW CREATE TABLE lifetime_statistics_users \G
*************************** 1. row ***************************
       Table: lifetime_statistics_users
Create Table: CREATE TABLE `lifetime_statistics_users` (
  `uuid` varchar(100) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `json` mediumtext DEFAULT NULL,
  `kills` bigint(20) DEFAULT NULL,
  `deaths` bigint(20) DEFAULT NULL,
  `playtime` bigint(20) DEFAULT NULL,
  `money` decimal(14,2) DEFAULT NULL,
  `level` bigint(20) DEFAULT NULL,
  `texture` longtext DEFAULT NULL,
  `blocks_broken` decimal(14,2) DEFAULT NULL,
  `blocks_placed` bigint(20) DEFAULT NULL,
  `mobs_killed` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `lifetime_statistics_users_index_uuid` (`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_name` (`name`) USING BTREE,
  KEY `lifetime_statistics_users_index_nameuuid` (`name`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_kills` (`kills`) USING BTREE,
  KEY `lifetime_statistics_users_index_killsuuid` (`kills`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_deaths` (`deaths`) USING BTREE,
  KEY `lifetime_statistics_users_index_deathsuuid` (`deaths`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_playtime` (`playtime`) USING BTREE,
  KEY `lifetime_statistics_users_index_playtimeuuid` (`playtime`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_money` (`money`) USING BTREE,
  KEY `lifetime_statistics_users_index_moneyuuid` (`money`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_level` (`level`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_broken` (`blocks_broken`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_brokenuuid` (`blocks_broken`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_placed` (`blocks_placed`) USING BTREE,
  KEY `lifetime_statistics_users_index_blocks_placeduuid` (`blocks_placed`,`uuid`) USING BTREE,
  KEY `lifetime_statistics_users_index_mobs_killed` (`mobs_killed`) USING BTREE,
  KEY `lifetime_statistics_users_index_mobs_killeduuid` (`mobs_killed`,`uuid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)
```

CodePudding user response：

Get rid of the FORCE INDEX clauses. That is causing the slowdown.

After that, show us the EXPLAIN again, but in text not as an essentially unreadable image.

(I don't have an answer to the 80ms vs 16ms)

More

That query is counting how many rows have kills > ... and coming up with 118K. This takes time. It cannot be speeded up. But you could change to percentile, then have a simple table of them.

Here's a thought... Keep the high-scorers doing the current COUNT, but limit that to users with more than X kills. X = somewhere around the 10th percentile. Then have a table of 100 percentiles that is periodically updated. That is, it is done in the background, not at the time when you are showing the user his ranking. That tiny table would have the number of kills for each of the 100 percentiles; the query would be a very fast index lookup, something:

SELECT ptile
    FROM week_ptiles
       WHERE ( SELECT kills FROM week_statistics_users
                  WHERE uuid='...'
             ) >= kills LIMIT 1;

CREATE TABLE week_ptiles (
    ptile TINYINT NOT NULL,
    kills BIGINT NOT NULL
    PRIMARY KEY(ptile),
    INDEX(kills, ptile)
) ENGINE=InnoDB;