MySQL index performance improvement - columns in multiple indexes?

Question

Trying to use indexes for massive data more efficiently.

I have an open source application that registers millions of records in a MySQL database. I have been using mysql databases in web development for many years, and I am quite versed in choosing effective field types, on the basics of why / how indexes are useful, etc., But the amount of data that we use in our application is coupled with the fact that it’s difficult to predict which columns will be requested, I’m a little under water.

The application registers events by players. We have a very developed cleaning system, but some servers are so busy that they have 50 million records in eight weeks.

In this size event with our existing indexes, queries can last 30-90 seconds.

Primary table layout (minus existing indexes):

CREATE TABLE IF NOT EXISTS `prism_data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `epoch` int(10) unsigned NOT NULL,
  `action_id` int(10) unsigned NOT NULL,
  `player_id` int(10) unsigned NOT NULL,
  `world_id` int(10) unsigned NOT NULL,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `z` int(11) NOT NULL,
  `block_id` mediumint(5) DEFAULT NULL,
  `block_subid` mediumint(5) DEFAULT NULL,
  `old_block_id` mediumint(5) DEFAULT NULL,
  `old_block_subid` mediumint(5) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

WHERE conditions most often include:

• world_id/x/y/z (all requests are equal by default to the radius around the user, so the coordinates are almost always used)
• epoch (all default requests for the last three days, users should override this for longer timeframes)
• action_idand / or player_id(Half the time, users are looking for who made specific actions or what actions a particular player triggered.)
• The remaining requests can be any combination, block_id values ​​in combination with a player or action, etc. Random

GROUP BY - , 100 //, .

action_id, player_id, block_id, DATE(FROM_UNIXTIME(epoch))

ORDER BY prism_data.epoch DESC, x ASC, z ASC, y ASC, id DESC. epoch , . - , "" .

/:

SELECT *
FROM prism_data 
INNER JOIN prism_players p ON p.player_id = prism_data.player_id
INNER JOIN prism_actions a ON a.action_id = prism_data.action_id
INNER JOIN prism_worlds w ON w.world_id = prism_data.world_id 
LEFT JOIN prism_data_extra ex ON ex.data_id = prism_data.id 
WHERE w.world = 'DeuxTiersMondes'
AND (prism_data.x BETWEEN 668 AND 868)
AND (prism_data.y BETWEEN -33 AND 167)
AND (prism_data.z BETWEEN 358 AND 558);
LIMIT 1000;

: INDEX location ( world_id , x , z , y ); 1000 ( 50 64735) 15 .

:

+----+-------------+------------+--------+---------------+----------+---------+--------------------------------+------+--------------------------+
| id | select_type | table      | type   | possible_keys | key      | key_len | ref                            | rows | Extra                    |
+----+-------------+------------+--------+---------------+----------+---------+--------------------------------+------+--------------------------+
|  1 | SIMPLE      | w          | ref    | PRIMARY,world | world    | 767     | const                          |    1 | Using where; Using index |
|  1 | SIMPLE      | prism_data | ref    | location      | location | 4       | minecraft.w.world_id           | 6155 | Using index condition    |
|  1 | SIMPLE      | a          | eq_ref | PRIMARY       | PRIMARY  | 4       | minecraft.prism_data.action_id |    1 | NULL                     |
|  1 | SIMPLE      | p          | eq_ref | PRIMARY       | PRIMARY  | 4       | minecraft.prism_data.player_id |    1 | NULL                     |
|  1 | SIMPLE      | ex         | ref    | data_id       | data_id  | 4       | minecraft.prism_data.id        |    1 | NULL                     |
+----+-------------+------------+--------+---------------+----------+---------+--------------------------------+------+--------------------------+

, , . / .

:

, , . , world_id/x/y/z, action_id/player_id epoch. , . , world_id, player_id, and epoch, world_id/x/y/z.

• / ? , world_id/player_id/epoch? , mysql, , , , mysql, . , .
• , , by/order by? Using filesort, , , .
• - , ?

.

5 , , . , -, , .

Answer 1

, : JOIN prism_worlds , ( ) . " , " - ". .

prism_worlds.world ,

SELECT *
FROM prism_data 
WHERE prism_data.world_id = (SELECT w.world_id FROM prism_worlds AS w WHERE w.world = 'DeuxTiersMondes')
LIMIT 1000;

, prism_data.world_id . MySQL , . . EXPLAIN const -subquery.

---

prism_data.x, .y .z: . , ( int) , , .

---

gazillions . . cron, ( ) , cron .

Answer 2

MySQL ( RDMS) . , , ,

SELECT prism_data.id,
       prism_data.action_id,
       prism_data.world_id
  FROM prism_data 
 INNER JOIN prism_worlds w ON w.world_id = prism_data.world_id 
 WHERE w.world = 'DeuxTiersMondes'
   AND (prism_data.x BETWEEN 668 AND 868)
   AND (prism_data.y BETWEEN -33 AND 167)
   AND (prism_data.z BETWEEN 358 AND 558)
 ORDER BY prism_data.id DESC
 LIMIT 1000;

BTREE prism_data, , ( - BTREE):

 (world_id, x, y, z, id, action_id, world_id)

prism_data . , , , - , . world_id, x, y z . id, LIMIT 1000.

SELECT *. SELECT *, MySQL - , , , .

x y, MyISAM, . x/y, .

; .

Answer 3

MySQL , n . , a, b, c, d, MySQL , a, b. OTOH, MySQL , B, C, D . , , . , // /.

I don't think MySQL ordered indexes, so I suspect indexing won't help when ordering performance, but I'm not sure.

Depending on how you use your data, it makes sense to investigate the partition of the table, perhaps by era.