I have two tables clients and shows.
Clients Table
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| clientID | int | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| routine | varchar(30) | YES | | NULL | |
| standardFee | decimal(5,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
Shows Table
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| showID | int | NO | PRI | NULL | |
| showDate | date | YES | | NULL | |
| clientID | int | YES | MUL | NULL | |
| attendance | int | YES | | NULL | |
| ticketPrice | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
The following is the data in the tables.
+----------+------------+------------------+-------------+
| clientID | name | routine | standardFee |
+----------+------------+------------------+-------------+
| 1 | Gladys | song and dance | 140.00 |
| 2 | Katherine | standup | 140.00 |
| 3 | Georgeanna | standup | 140.00 |
| 4 | Wanda | song and dance | 140.00 |
| 5 | Ann | trained squirrel | 79.99 |
+----------+------------+------------------+-------------+
+--------+------------+----------+------------+-------------+
| showID | showDate | clientID | attendance | ticketPrice |
+--------+------------+----------+------------+-------------+
| 1 | 2019-12-25 | 4 | 144 | 22.50 |
| 2 | 2020-01-11 | 5 | 84 | 29.50 |
| 3 | 2020-01-17 | 3 | 190 | 19.49 |
| 4 | 2020-01-31 | 5 | 234 | 14.99 |
| 5 | 2020-02-08 | 1 | 86 | 25.00 |
| 6 | 2020-02-14 | 3 | 122 | 59.25 |
| 7 | 2020-02-15 | 2 | 101 | 26.50 |
| 8 | 2020-02-27 | 2 | 186 | 19.99 |
| 9 | 2020-03-06 | 4 | 222 | 45.00 |
| 10 | 2020-03-07 | 5 | 250 | 8.99 |
+--------+------------+----------+------------+-------------+
I am trying to understand the following query
EXPLAIN UPDATE
clients JOIN shows
USING (clientID)
SET clients.routine = 'mime',
shows.ticketPrice = 30
WHERE name = 'Gladys' or name = 'Ann';
MySql returned the following
+----+-------------+---------+------------+------+---------------+-----------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+-----------+---------+---------------------------+------+----------+-------------+
| 1 | UPDATE | clients | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 36.00 | Using where |
| 1 | UPDATE | shows | NULL | ref | client_fk | client_fk | 5 | updatedb.clients.clientID | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+-----------+---------+---------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
I am wondering why mysql went through only 1 row for the shows table when the update happened on Four of its rows where clientID is 5 and clientID is 1.
I went through how indexes work. I am finding it difficult to understand query execution on two tables when Indexes are involved.
