3

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?

  • A (id) is a foreign key referencing another table (lets call it tableUser) where it is a primary key and unique – spacing Dec 16 '16 at 16:06
  • The best answer is likely to involve getting rid of GROUP BY. But to do that, I need to understand what the query is trying to do. (TableA and TableB obfuscate things.) – Rick James Dec 18 '16 at 1:54
  • 1
    SHOW CREATE TABLE is more descriptive than DESC; please switch to that. – Rick James Dec 18 '16 at 1:55
  • This is a dup of stackoverflow.com/questions/41187911/… , which has one (different) answer so far. – Rick James Dec 18 '16 at 2:43
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.