Mysql – Compound index on inner join table

clustered-indexindexindex-tuningMySQL

I'm trying to create additional index or indexes to speed up the query below. All the of the keys are primary keys (except for id on Table A) so they already have a default btree index associated with them. id on table A also has a index already associated with it since its a MUL key which means it's part of a non unique index.

Select A.id
From TableA A
      Inner join TableB B
      On A.address = B.address
      And A.code = B.code
Group by A.id
Having count(distinct B.user) = 1;

These are the current index on the mentioned tables:

mysql> show index from TableA;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TableA  |          0 | PRIMARY  |            1 | address     | A         |           8 |     NULL | NULL   |      | BTREE      |         |
| TableA  |          0 | PRIMARY  |            2 | code        | A         |          24 |     NULL | NULL   |      | BTREE      |         |
| TableA  |          1 | id       |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+


mysql> show index from TableB;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| TableB    |          0 | PRIMARY  |            1 | user        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          0 | PRIMARY  |            2 | address     | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          0 | PRIMARY  |            3 | code        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          1 | address  |            1 | address     | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| TableB    |          1 | address  |            2 | code        | A         |           9 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

The query explain states this:

+----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                                   | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | F     | index  | address       | address | 514     | NULL                                  |    9 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | A     | eq_ref | PRIMARY       | PRIMARY | 514     | db.B.address,db.B.code                |    1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------------------+------+----------------------------------------------+

I'm having trouble understanding what I should do. Is a compound index of address and code the only thing that I would be able to do to improve the speed of the mentioned query?

Or is a clustered index of id (since the query uses a group by) better? Or could I use both?

Best Answer

Some thoughts:

I don't know your existing schema but going off your query alone, I'd try out an additional compound index of (address, code) on table A, and a compound index of (address, code, user).

That's because your compound indexes should always be in the correct order of how your WHERE-ing them in your query or JOIN-ing on them.

When I say WHERE in the clauses below, these can also be applied to JOINs. When you JOIN two tables together, the engine has to search for appropriate rows based on your ON clause, so you need to think of them as equivalent.

Here's an algorithm for finding the best indexes (this is Rick James's index cookbook):

  1. As many columns in your query where you have WHERE column = 'foo', in any order

  2. Then, pick one from the below three options:

    a. Any column with a range select, e.g. WHERE column > 'foo' AND column < 'bar'

    b. Any column(s) in a GROUP BY provided no mixing of ASC/DESC

    c. Any column(s) in an ORDER BY provided no mixing of ASC/DESC

Also, is id the PK of table A? If so, remember that PKs are always appended on the end of every index in MySQL - so you should never need to append those columns on any other index, the engine will do it for you.

Related Question