mysql: why below query unused union index?

Question

table a
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| uid      | int(11)     | YES  | MUL | NULL    |       |
| channel  | varchar(20) | YES  |     | NULL    |       |
| createAt | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

table a index: a_index_uid_createAt` (`uid`,`createAt`)


table b:
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| uid       | int(11)     | NO   | PRI | NULL    |       |
| date      | date        | YES  | MUL | NULL    |       |
| channel   | varchar(20) | YES  | MUL | NULL    |       |
| gender    | smallint(6) | YES  | MUL | NULL    |       |
| chargeAmt | int(11)     | YES  |     | 0       |       |
| revised   | smallint(6) | YES  |     | 0       |       |
| createAt  | datetime    | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

query st:

select  DATE(a.createAt) date,a.channel,b.chargeAmt
    FROM  a, b
    where  a.uid = b.uid
      and  a.createAt >= '2021-05-10 00:00:00'
      and  a.createAt <= '2021-05-10 23:59:59';

explain:

+----+-------------+-------+--------+-------------------------------------------------+---------+---------+--------------+--------+-------------+
| id | select_type | table | type   | possible_keys                                   | key     | key_len | ref          | rows   | Extra       |
+----+-------------+-------+--------+-------------------------------------------------+---------+---------+--------------+--------+-------------+
|  1 | SIMPLE      | a     | ALL    | a_index_uid_createAt | NULL    | NULL    | NULL         | 172725 | Using where |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY                                         | PRIMARY | 4       | xiehou.r.uid |      1 |             |
+----+-------------+-------+--------+-------------------------------------------------+---------+---------+--------------+--------+-------------+

why? a_index_uid_createAt index invalid!

Answer 1

Please use the JOIN .. ON syntax:

select  DATE(a.createAt) date, a.channel, b.chargeAmt
    FROM  a
    JOIN  b  ON  a.uid = b.uid   -- How the tables are related
    WHERE  a.createAt >= '2021-05-10                    -- filtering
      and  a.createAt  < '2021-05-10 + INTERVAL 1 DAY;  -- filtering

The Optimizer, when it sees a JOIN, starts by deciding which table to start with. The preferred table is the one with filtering, namely a.

To do the filtering, it needs an index that starts with the columns mentioned in the WHERE clause.

The other table will be reached by looking at the ON, which seems to have PRIMARY KEY(uid)

So, the only useful index is

a:  INDEX(createAt)

Any INDEX(uid, ...) is likely to be unused, since it starts with an existing index, namely PRIMARY KEY(uid).

(In the future, please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)