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!
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.)