Compound index on inner join table?

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 |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+-----

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):

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

-
Then, pick one from the below three options:

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

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.

StackExchange Database Administrators Q#158385, answer score: 2