0

I have a very simple MySQL update query on my InnoDB table. 

UPDATE `players_teams` SET t_last_active=NOW() WHERE t_player_id=11225 AND t_team_id=6912 AND t_season_id=2002 LIMIT 1

My table is structured as so:

CREATE TABLE `players_teams` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `t_player_id` int(11) DEFAULT NULL,
  `t_team_id` int(11) DEFAULT NULL,
  `t_league_id` int(11) DEFAULT NULL,
  `t_season_id` int(11) DEFAULT NULL,
  `t_div` varchar(64) DEFAULT NULL,
  `t_player_number` varchar(3) DEFAULT NULL,
  `t_player_jersey_size` enum('UNKNOWN','XS','S','M','L','XL','XXL','XXXL') DEFAULT 'UNKNOWN',
  `t_player_registration_number` varchar(64) DEFAULT NULL,
  `t_player_class` enum('ROSTER','SPARE','COACH','INJURED','HOLIDAY','SUSPENDED','SCOREKEEPER') DEFAULT 'ROSTER',
  `t_access_level` enum('PLAYER','MANAGER','ASSISTANT') DEFAULT 'PLAYER',
  `t_player_position` enum('ANY','FORWARD','DEFENCE','GOALIE','PITCHER','CATCHER','FIRST BASE','SECOND BASE','THIRD BASE','SHORTSTOP','LEFT FIELD','CENTER FIELD','RIGHT FIELD') DEFAULT 'ANY',
  `t_spare_status` enum('INVITED','IN','OUT') DEFAULT NULL,
  `t_drink_next` int(1) DEFAULT '0',
  `t_no_fees` tinyint(1) DEFAULT '0',
  `t_no_drink` tinyint(1) DEFAULT '0',
  `t_auto_check_in` tinyint(1) DEFAULT '0',
  `t_print_reminder` tinyint(1) DEFAULT '0',
  `t_notes` text,
  `t_last_chatter_id` int(11) DEFAULT NULL,
  `t_last_history_id` int(11) DEFAULT NULL,
  `t_last_active` timestamp NULL DEFAULT NULL,
  `t_status` enum('ACTIVE','INACTIVE','ARCHIVED') DEFAULT 'ACTIVE',
  `t_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `t_player_id` (`t_player_id`),
  KEY `t_team_id` (`t_team_id`),
  KEY `t_season_id` (`t_season_id`),
  KEY `t_player_id_2` (`t_player_id`,`t_team_id`,`t_season_id`),
  KEY `Team/Player ID` (`t_team_id`,`t_player_id`),
  KEY `UpdatePlayersDiv` (`t_team_id`,`t_season_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23454 DEFAULT CHARSET=latin1;

This simple update query takes on average, 3.5 seconds? I'm running this on a MediaTemple MySQL GRID container.

Here is the result of an EXPLAIN when switching the UPDATE to a SELECT.

Results of EXPLAIN

Can someone provide some insight on how I'm not doing this correctly?

[EDIT: Added list of Indexes]

So is this a big mess of indexes? Do I have a bunch of redundant indexes in here?

enter image description here

Cheers, Jon

Jon
  • 137
  • 2
  • 15
  • try `EXPLAIN` on the query, see if it's using your index on the three columns. You'll need to change it to a `select` though. – twentylemon Jun 13 '15 at 16:43
  • Sorry, I should have included my EXPLAIN results as well. I'll add that now. – Jon Jun 13 '15 at 16:47
  • 1
    @Jon your index t_player_id is actually redundant, because mysql can use t_player_id_2 to do the same thing with it (from multicolumn index any prefix can be used so index on (player, team, season) can be used as (player, team) and (player) directly - so just remove the single column one and mysql should have not much choice but use the right one (why it does not use it now is for deeper search - it might be wrong statistics, bad cardinality or optimizer hiccup) – jkavalik Jun 13 '15 at 18:49
  • Thanks @user1786423. I didn't know that. I'll give that a try. – Jon Jun 14 '15 at 07:16
  • @Jon for the edit - the same is true for t_team_id which can be substituted by both `Team/Player ID` and UpdatePlayersDiv. But other than that there seems to be no more index which is prefix of other one. So no "big mess" :) You can check your entire DB using Percona tool [pt-duplicate-key-checker](https://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html) – jkavalik Jun 14 '15 at 07:45
  • @user1786423 I feel like I need to re-create my indexes. They aren't even labeled nicely :) – Jon Jun 14 '15 at 07:48
  • @user1786423 I wish I were confident enough with my abilities to install that tool, but I really don't think I should, as I'll probably take down my entire DB. Maybe it's time I hire a real database admin to take a look at my structures and data. – Jon Jun 14 '15 at 16:48
  • @Jon Percona tools are usually quite safe and this one uses only read-only access ("show create table"), it is just some (i think perl) scripts. – jkavalik Jun 14 '15 at 18:21
  • @Jon and you can always run it on testing install/server to be really safe – jkavalik Jun 14 '15 at 19:02
  • @user1786423 ok, I'll try to give that a shot. Thanks for all the help. My Media Temple MySQL grid container is crashing every 12 hours or less. I think I need to get off shared hosting. – Jon Jun 15 '15 at 14:37

1 Answers1

1

It's using the wrong key instead of the index on the 3 columns. You can hint at indexes with USE KEY ... syntax.

https://dev.mysql.com/doc/refman/5.1/en/index-hints.html

You may also want to try reordering your key on the 3 columns. Generally, you want the most restricting column to be first in the index, then the next most restricting and so on.

twentylemon
  • 1,248
  • 9
  • 11
  • What exactly do you mean by restricting? So in this case, would I use USE INDEX (t_player_id_2) ?. Sorry, I'm quite green when it comes to MySQL. – Jon Jun 13 '15 at 16:55
  • Restricting as in, if split the table into chunks where each chunk had only one distinct value of the column, the first column in the index should be the one that splits the table into the most chunks. Then in each of those chunks, choose the column that would split them into the most sub-chunks and so on. Make sense? – twentylemon Jun 13 '15 at 17:00
  • Ah, so really it should be Player, Season and then Team? I'm sure it's hard for you to really know because you don't know how my entire database is structured. – Jon Jun 13 '15 at 17:04
  • Yeah, you'll have to figure that out yourself. You know your data better than I ever could. – twentylemon Jun 13 '15 at 17:05
  • I'm going to mark your answer as correct, as I think it will lead me down the right path. Thank you for your time! – Jon Jun 13 '15 at 18:07