title: MySQL/MariaDB: How to create index for group query with self-join inner query?
body: <p>In my MariaDB database I have this query I am hoping to speed up:</p>
  <pre class="lang-sql prettyprint-override"><code>select
    move,
    color,
    count(*) as count
  from
    (
      select
        b2.move,
        b2.color
      from
        game_fen_bridge b1
        inner join game_fen_bridge b2 on b1.game_id = b2.game_id
      where
        b2.ply &gt; b1.ply
        and b2.ply &lt; b1.ply + 10
        and b1.fen_id = 301
    ) inner_query
  group by
    move,
    color;
  </code></pre>
  <p>It takes about 1.7 seconds, which is not great since it's needed by the UI. Also it'll get longer as the data grows if I don't do something about it. Here's the EXPLAIN:</p>
  <pre><code>+------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
  | id   | select_type | table | type | possible_keys                         | key     | key_len | ref                         | rows  | Extra                                                     |
  +------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
  |    1 | SIMPLE      | b1    | ref  | game_id,game_id_2,pgf,pfg,gpf,fpg,fgp | fpg     | 9       | const                       | 78964 | Using where; Using index; Using temporary; Using filesort |
  |    1 | SIMPLE      | b2    | ref  | game_id,game_id_2,pgf,pfg,gpf         | game_id | 9       | danbockn_gambase.b1.game_id |    37 | Using index condition                                     |
  +------+-------------+-------+------+---------------------------------------+---------+---------+-----------------------------+-------+-----------------------------------------------------------+
  </code></pre>
  <p>As you can see, it has quite a few indexes to choose from. Before posting here, I tried to solve the problem by brute force by creating an index using every permutation of game_id, fen_id and ply. Nothing changes the database's insistence on scanning 78,964 rows.</p>
  <p>Here's the table. It has about 8.4 million rows. 39,701 of them match the fen_id from the where clause, 301.</p>
  <pre class="lang-sql prettyprint-override"><code>create table game_fen_bridge (
    id bigint unsigned auto_increment primary key,
    game_id bigint unsigned,
    fen_id bigint unsigned,
    move varchar(7),
    move_number tinyint unsigned,
    ply tinyint unsigned,
    color enum('w', 'b'),
    constraint foreign key (game_id) references games (game_id),
    constraint foreign key (fen_id) references fens (fen_id),
    unique(game_id, fen_id, ply),
    unique(game_id, fen_id, move_number, color)
  );
  </code></pre>
  <p>Is there a way to speed up this query?</p>
  <p>Updated: example data and output</p>
  <pre><code>+---------+----------+---------+-------+-------------+------+-------+
  | id      | game_id  | fen_id  | move  | move_number | ply  | color |
  +---------+----------+---------+-------+-------------+------+-------+
  | 6483612 |  9866652 |     301 | d4    |           1 |    1 | w     |
  | 6483613 |  9866652 |     302 | d5    |           1 |    2 | b     |
  | 6483616 |  9866652 |    1853 | Nf3   |           3 |    5 | w     |
  | 6483614 |  9866652 |    3101 | Bf4   |           2 |    3 | w     |
  | 6483615 |  9866652 |   44813 | e6    |           2 |    4 | b     |
  | 6483617 |  9866652 |  243743 | c5    |           3 |    6 | b     |
  | 6483618 |  9866652 |  243744 | e3    |           4 |    7 | w     |
  | 6483619 |  9866652 |  243745 | Nc6   |           4 |    8 | b     |
  | 6483620 |  9866652 | 2103234 | Bb5   |           5 |    9 | w     |
  | 6483621 |  9866652 | 2103235 | Bd7   |           5 |   10 | b     |
  | 6483622 |  9866652 | 5444081 | c3    |           6 |   11 | w     |
  | 6483623 |  9866652 | 5444082 | f5    |           6 |   12 | b     |
  | 6483624 |  9866652 | 5444083 | Bxc6  |           7 |   13 | w     |
  | 6483625 |  9866652 | 5444084 | Bxc6  |           7 |   14 | b     |
  | 6483626 |  9866652 | 5444085 | O-O   |           8 |   15 | w     |
  | 6483627 |  9866652 | 5444086 | Nf6   |           8 |   16 | b     |
  | 6483628 |  9866652 | 5444087 | Ng5   |           9 |   17 | w     |
  | 6483629 |  9866652 | 5444088 | Bd7   |           9 |   18 | b     |
  | 6483630 |  9866652 | 5444089 | dxc5  |          10 |   19 | w     |
  | 6483631 |  9866652 | 5444090 | Bxc5  |          10 |   20 | b     |
  | 6483632 |  9866652 | 5444091 | Be5   |          11 |   21 | w     |
  | 6483633 |  9866652 | 5444092 | O-O   |          11 |   22 | b     |
  | 6483634 |  9866652 | 5444093 | Bxf6  |          12 |   23 | w     |
  | 6483635 |  9866652 | 5444094 | Qxf6  |          12 |   24 | b     |
  | 6483636 |  9866652 | 5444095 | Qh5   |          13 |   25 | w     |
  | 6483637 |  9866652 | 5444096 | h6    |          13 |   26 | b     |
  | 6483638 |  9866652 | 5444097 | Nh3   |          14 |   27 | w     |
  | 6483639 |  9866652 | 5444098 | g5    |          14 |   28 | b     |
  | 6483640 |  9866652 | 5444099 | b4    |          15 |   29 | w     |
  | 6483641 |  9866652 | 5444100 | Bxb4  |          15 |   30 | b     |
  | 6483642 |  9866652 | 5444101 | Rc1   |          16 |   31 | w     |
  | 6483643 |  9866652 | 5444102 | Rac8  |          16 |   32 | b     |
  | 6483644 |  9866652 | 5444103 | a3    |          17 |   33 | w     |
  | 6483645 |  9866652 | 5444104 | Bxc3  |          17 |   34 | b     |
  | 6483646 |  9866652 | 5444105 | Nxc3  |          18 |   35 | w     |
  | 6483647 |  9866652 | 5444106 | Rxc3  |          18 |   36 | b     |
  | 6483648 |  9866652 | 5444107 | Rab1  |          19 |   37 | w     |
  | 6483649 |  9866652 | 5444108 | Rfc8  |          19 |   38 | b     |
  | 6483650 |  9866652 | 5444109 | Rd1   |          20 |   39 | w     |
  | 6483651 |  9866652 | 5444110 | Ba4   |          20 |   40 | b     |
  | 6483652 |  9866652 | 5444111 | Rf1   |          21 |   41 | w     |
  | 6483653 |  9866652 | 5444112 | b5    |          21 |   42 | b     |
  | 6483654 |  9866652 | 5444113 | f4    |          22 |   43 | w     |
  | 6483655 |  9866652 | 5444114 | g4    |          22 |   44 | b     |
  | 6483656 |  9866652 | 5444115 | Nf2   |          23 |   45 | w     |
  | 6483657 |  9866652 | 5444116 | Rxe3  |          23 |   46 | b     |
  | 6483658 |  9866652 | 5444117 | g3    |          24 |   47 | w     |
  | 6483659 |  9866652 | 5444118 | b4    |          24 |   48 | b     |
  | 6483660 |  9866652 | 5444119 | Rxb4  |          25 |   49 | w     |
  | 6483661 |  9866652 | 5444120 | Be8   |          25 |   50 | b     |
  | 6483662 |  9866652 | 5444121 | Qh4   |          26 |   51 | w     |
  | 6483663 |  9866652 | 5444122 | Qxh4  |          26 |   52 | b     |
  | 6483664 |  9866652 | 5444123 | gxh4  |          27 |   53 | w     |
  | 6483665 |  9866652 | 5444124 | Rc4   |          27 |   54 | b     |
  | 6483666 |  9866652 | 5444125 | Rb8   |          28 |   55 | w     |
  | 6483667 |  9866652 | 5444126 | Kf7   |          28 |   56 | b     |
  | 6483668 |  9866652 | 5444127 | Rd1   |          29 |   57 | w     |
  | 6483669 |  9866652 | 5444128 | Rxf4  |          29 |   58 | b     |
  | 6483670 |  9866652 | 5444129 | Nd3   |          30 |   59 | w     |
  | 6483671 |  9866652 | 5444130 | Rff3  |          30 |   60 | b     |
  | 6483672 |  9866652 | 5444131 | Rb7+  |          31 |   61 | w     |
  | 6483673 |  9866652 | 5444132 | Kf6   |          31 |   62 | b     |
  | 6483674 |  9866652 | 5444133 | Nc5   |          32 |   63 | w     |
  | 6483675 |  9866652 | 5444134 | Rxa3  |          32 |   64 | b     |
  | 6483676 |  9866652 | 5444135 | Re1   |          33 |   65 | w     |
  | 6483677 |  9866652 | 5444136 | e5    |          33 |   66 | b     |
  | 6483678 |  9866652 | 5444137 | Rb8   |          34 |   67 | w     |
  | 6483679 |  9866652 | 5444138 | Bc6   |          34 |   68 | b     |
  | 6483680 |  9866652 | 5444139 | Rc8   |          35 |   69 | w     |
  | 6483681 |  9866652 | 5444140 | Bb5   |          35 |   70 | b     |
  | 6483218 | 10757193 |     301 | d4    |           1 |    1 | w     |
  | 6483219 | 10757193 |     395 | Nf6   |           1 |    2 | b     |
  | 6483220 | 10757193 |     396 | c4    |           2 |    3 | w     |
  | 6483221 | 10757193 |     592 | e6    |           2 |    4 | b     |
  | 6483222 | 10757193 |   23786 | Nf3   |           3 |    5 | w     |
  | 6483223 | 10757193 |   29571 | b6    |           3 |    6 | b     |
  | 6483224 | 10757193 |   29572 | g3    |           4 |    7 | w     |
  | 6483228 | 10757193 |   49264 | O-O   |           6 |   11 | w     |
  | 6483229 | 10757193 |   49265 | O-O   |           6 |   12 | b     |
  | 6483230 | 10757193 |   49266 | Nc3   |           7 |   13 | w     |
  | 6483231 | 10757193 |   49267 | Ne4   |           7 |   14 | b     |
  | 6483225 | 10757193 |   51255 | Bb7   |           4 |    8 | b     |
  | 6483226 | 10757193 |   51256 | Bg2   |           5 |    9 | w     |
  | 6483227 | 10757193 |  117266 | Be7   |           5 |   10 | b     |
  | 6483232 | 10757193 |  259277 | Qc2   |           8 |   15 | w     |
  | 6483233 | 10757193 |  259278 | Nxc3  |           8 |   16 | b     |
  | 6483234 | 10757193 |  259279 | Qxc3  |           9 |   17 | w     |
  | 6483235 | 10757193 | 1441865 | c5    |           9 |   18 | b     |
  | 6483236 | 10757193 | 5443779 | b3    |          10 |   19 | w     |
  | 6483237 | 10757193 | 5443780 | Bf6   |          10 |   20 | b     |
  | 6483238 | 10757193 | 5443781 | Bb2   |          11 |   21 | w     |
  | 6483239 | 10757193 | 5443782 | d6    |          11 |   22 | b     |
  | 6483240 | 10757193 | 5443783 | e3    |          12 |   23 | w     |
  | 6483241 | 10757193 | 5443784 | Nd7   |          12 |   24 | b     |
  | 6483242 | 10757193 | 5443785 | Rad1  |          13 |   25 | w     |
  | 6483243 | 10757193 | 5443786 | Qc7   |          13 |   26 | b     |
  | 6483244 | 10757193 | 5443787 | Rd2   |          14 |   27 | w     |
  | 6483245 | 10757193 | 5443788 | Rac8  |          14 |   28 | b     |
  | 6483246 | 10757193 | 5443789 | Rfd1  |          15 |   29 | w     |
  | 6483247 | 10757193 | 5443790 | cxd4  |          15 |   30 | b     |
  | 6483248 | 10757193 | 5443791 | Nxd4  |          16 |   31 | w     |
  | 6483249 | 10757193 | 5443792 | Bxg2  |          16 |   32 | b     |
  | 6483250 | 10757193 | 5443793 | Kxg2  |          17 |   33 | w     |
  | 6483251 | 10757193 | 5443794 | b5    |          17 |   34 | b     |
  | 6483252 | 10757193 | 5443795 | Qb4   |          18 |   35 | w     |
  | 6483253 | 10757193 | 5443796 | bxc4  |          18 |   36 | b     |
  | 6483254 | 10757193 | 5443797 | Nb5   |          19 |   37 | w     |
  | 6483255 | 10757193 | 5443798 | Qb7+  |          19 |   38 | b     |
  | 6483256 | 10757193 | 5443799 | f3    |          20 |   39 | w     |
  | 6483257 | 10757193 | 5443800 | Bxb2  |          20 |   40 | b     |
  | 6483258 | 10757193 | 5443801 | Rxb2  |          21 |   41 | w     |
  | 6483259 | 10757193 | 5443802 | Ne5   |          21 |   42 | b     |
  | 6483260 | 10757193 | 5443803 | e4    |          22 |   43 | w     |
  | 6483261 | 10757193 | 5443804 | Nd3   |          22 |   44 | b     |
  | 4665951 | 13090397 |     301 | d4    |           1 |    1 | w     |
  | 4665952 | 13090397 |     395 | Nf6   |           1 |    2 | b     |
  | 4665953 | 13090397 |     901 | Nf3   |           2 |    3 | w     |
  | 4665954 | 13090397 |    3041 | e6    |           2 |    4 | b     |
  | 4665956 | 13090397 |   52873 | d5    |           3 |    6 | b     |
  | 4665955 | 13090397 |   76756 | e3    |           3 |    5 | w     |
  | 4665957 | 13090397 | 3944440 | Ne5   |           4 |    7 | w     |
  | 4665958 | 13090397 | 3944441 | Nbd7  |           4 |    8 | b     |
  | 4665959 | 13090397 | 3944442 | f4    |           5 |    9 | w     |
  | 4665960 | 13090397 | 3944443 | Be7   |           5 |   10 | b     |
  | 4665961 | 13090397 | 3944444 | Bd3   |           6 |   11 | w     |
  | 4665962 | 13090397 | 3944445 | O-O   |           6 |   12 | b     |
  | 4665963 | 13090397 | 3944446 | O-O   |           7 |   13 | w     |
  | 4665964 | 13090397 | 3944447 | c5    |           7 |   14 | b     |
  | 4665965 | 13090397 | 3944448 | c3    |           8 |   15 | w     |
  | 4665966 | 13090397 | 3944449 | Qc7   |           8 |   16 | b     |
  | 4665967 | 13090397 | 3944450 | Qf3   |           9 |   17 | w     |
  | 4665968 | 13090397 | 3944451 | b6    |           9 |   18 | b     |
  | 4665969 | 13090397 | 3944452 | Nd2   |          10 |   19 | w     |
  | 4665970 | 13090397 | 3944453 | Bb7   |          10 |   20 | b     |
  | 4665971 | 13090397 | 3944454 | Rf2   |          11 |   21 | w     |
  | 4665972 | 13090397 | 3944455 | g6    |          11 |   22 | b     |
  | 4665973 | 13090397 | 3944456 | Qe2   |          12 |   23 | w     |
  | 4665974 | 13090397 | 3944457 | Ne4   |          12 |   24 | b     |
  | 4665975 | 13090397 | 3944458 | Rf3   |          13 |   25 | w     |
  | 4665976 | 13090397 | 3944459 | Ndf6  |          13 |   26 | b     |
  | 4665977 | 13090397 | 3944460 | Rh3   |          14 |   27 | w     |
  | 4665978 | 13090397 | 3944461 | h5    |          14 |   28 | b     |
  | 4665979 | 13090397 | 3944462 | Ndf3  |          15 |   29 | w     |
  | 4665980 | 13090397 | 3944463 | Kg7   |          15 |   30 | b     |
  | 4665981 | 13090397 | 3944464 | Bd2   |          16 |   31 | w     |
  | 4665982 | 13090397 | 3944465 | Ng4   |          16 |   32 | b     |
  | 4665983 | 13090397 | 3944466 | Bxe4  |          17 |   33 | w     |
  | 4665984 | 13090397 | 3944467 | dxe4  |          17 |   34 | b     |
  | 4665985 | 13090397 | 3944468 | Nh4   |          18 |   35 | w     |
  | 4665986 | 13090397 | 3944469 | Nxe5  |          18 |   36 | b     |
  | 4665987 | 13090397 | 3944470 | fxe5  |          19 |   37 | w     |
  | 4665988 | 13090397 | 3944471 | Bxh4  |          19 |   38 | b     |
  | 4665989 | 13090397 | 3944472 | Rxh4  |          20 |   39 | w     |
  | 4665990 | 13090397 | 3944473 | Qe7   |          20 |   40 | b     |
  | 4665991 | 13090397 | 3944474 | Rh3   |          21 |   41 | w     |
  | 4665992 | 13090397 | 3944475 | f6    |          21 |   42 | b     |
  | 4665993 | 13090397 | 3944476 | Rg3   |          22 |   43 | w     |
  | 4665994 | 13090397 | 3944477 | Kh6   |          22 |   44 | b     |
  | 4665995 | 13090397 | 3944478 | Rf1   |          23 |   45 | w     |
  | 4665996 | 13090397 | 3944479 | fxe5  |          23 |   46 | b     |
  | 4665997 | 13090397 | 3944480 | Rf4   |          24 |   47 | w     |
  | 4665998 | 13090397 | 3944481 | exf4  |          24 |   48 | b     |
  | 4665999 | 13090397 | 3944482 | exf4  |          25 |   49 | w     |
  | 4666000 | 13090397 | 3944483 | Rf5   |          25 |   50 | b     |
  | 4666001 | 13090397 | 3944484 | Rh3   |          26 |   51 | w     |
  | 4666002 | 13090397 | 3944485 | Kg7   |          26 |   52 | b     |
  | 4666003 | 13090397 | 3944486 | g4    |          27 |   53 | w     |
  | 4666004 | 13090397 | 3944487 | hxg4  |          27 |   54 | b     |
  | 4666005 | 13090397 | 3944488 | Qxg4  |          28 |   55 | w     |
  | 4666006 | 13090397 | 3944489 | Rh8   |          28 |   56 | b     |
  | 4666007 | 13090397 | 3944490 | Rg3   |          29 |   57 | w     |
  | 4666008 | 13090397 | 3944491 | Rh6   |          29 |   58 | b     |
  | 4666009 | 13090397 | 3944492 | Rh3   |          30 |   59 | w     |
  | 4666010 | 13090397 | 3944493 | Rxh3  |          30 |   60 | b     |
  | 4666011 | 13090397 | 3944494 | Qxh3  |          31 |   61 | w     |
  | 4666012 | 13090397 | 3944495 | Qf8   |          31 |   62 | b     |
  | 4666013 | 13090397 | 3944496 | Qg4   |          32 |   63 | w     |
  | 4666014 | 13090397 | 3944497 | Qh8   |          32 |   64 | b     |
  | 4666015 | 13090397 | 3944498 | dxc5  |          33 |   65 | w     |
  | 4666016 | 13090397 | 3944499 | Qh5   |          33 |   66 | b     |
  | 4666017 | 13090397 | 3944500 | h3    |          34 |   67 | w     |
  | 4666018 | 13090397 | 3944501 | Qxg4+ |          34 |   68 | b     |
  | 4666019 | 13090397 | 3944502 | hxg4  |          35 |   69 | w     |
  | 4666020 | 13090397 | 3944503 | Rxc5  |          35 |   70 | b     |
  | 4666021 | 13090397 | 3944504 | Kf2   |          36 |   71 | w     |
  | 4666022 | 13090397 | 3944505 | Kf6   |          36 |   72 | b     |
  | 4666023 | 13090397 | 3944506 | Be3   |          37 |   73 | w     |
  | 4666024 | 13090397 | 3944507 | Rd5   |          37 |   74 | b     |
  | 4666025 | 13090397 | 3944508 | Bd4+  |          38 |   75 | w     |
  | 4666026 | 13090397 | 3944509 | Rxd4  |          38 |   76 | b     |
  | 4666027 | 13090397 | 3944510 | cxd4  |          39 |   77 | w     |
  | 4666028 | 13090397 | 3944511 | g5    |          39 |   78 | b     |
  | 4666029 | 13090397 | 3944512 | Ke3   |          40 |   79 | w     |
  | 4666030 | 13090397 | 3944513 | Bd5   |          40 |   80 | b     |
  | 4666031 | 13090397 | 3944514 | a3    |          41 |   81 | w     |
  | 4666032 | 13090397 | 3944515 | gxf4+ |          41 |   82 | b     |
  | 4666033 | 13090397 | 3944516 | Kxf4  |          42 |   83 | w     |
  | 4666034 | 13090397 | 3944517 | Kg6   |          42 |   84 | b     |
  | 4666035 | 13090397 | 3944518 | b4    |          43 |   85 | w     |
  | 4666036 | 13090397 | 3944519 | b5    |          43 |   86 | b     |
  | 4666037 | 13090397 | 3944520 | g5    |          44 |   87 | w     |
  | 4666038 | 13090397 | 3944521 | Kh5   |          44 |   88 | b     |
  +---------+----------+---------+-------+-------------+------+-------+
  </code></pre>
  <pre><code>+------+-------+-------+
  | move | color | count |
  +------+-------+-------+
  | b6   | b     |     1 |
  | Bb5  | w     |     1 |
  | Bb7  | b     |     1 |
  | Bd7  | b     |     1 |
  | Be7  | b     |     2 |
  | Bf4  | w     |     1 |
  | Bg2  | w     |     1 |
  | c4   | w     |     1 |
  | c5   | b     |     1 |
  | d5   | b     |     2 |
  | e3   | w     |     2 |
  | e6   | b     |     3 |
  | f4   | w     |     1 |
  | g3   | w     |     1 |
  | Nbd7 | b     |     1 |
  | Nc6  | b     |     1 |
  | Ne5  | w     |     1 |
  | Nf3  | w     |     3 |
  | Nf6  | b     |     2 |
  +------+-------+-------+
  </code></pre>