I created two tables with one-to-one relationship as below,
CREATE TABLE if not exists User (
id bigint AUTO_INCREMENT,
coins bigint not null,
level bigint not null,
PRIMARY KEY (id)
);
CREATE TABLE if not exists Claimingcoins (
userid bigint UNIQUE NOT NULL,
claimingCoins bigint
);
ALTER TABLE Claimingcoins ADD CONSTRAINT fk_user_id FOREIGN KEY (userid) REFERENCES `User` (id);
Now I run EXPLAIN query on some statements to see if CLUSTERED INDEX is set on both these entities without me having to set it. I use mysql 8.0.28, I suppose it sets it by default. Does it also set it for UNIQUE KEY which is referenced like above?
mysql> EXPLAIN UPDATE Claimingcoins SET claimingCoins = 100 where userid IN(2999999);
+----+-------------+---------------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
| 1 | UPDATE | Claimingcoins | NULL | range | userid | userid | 8 | const | 1 | 100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0,00 sec)
mysql> EXPLAIN SELECT id,coins,level FROM User WHERE id IN (29999, 299, 299999);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | User | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
I ran the EXPLAIN statements for the queries and I get the results above. How can I understand by looking at this table if clustered index is set? I see possible_keys and key values are exposed, so I believed those should be the one used for CLUSTERED INDEXES, right?
1 Answer 1
There is a rules set which allows to determine what index will be used as clustered one. But they needs in deep CREATE TABLE script scanning.
There exists another way. Query INFORMATION_SCHEMA tables
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database name'
AND TABLE_NAME = 'table name'
AND COLUMN_KEY = 'PRI';
If some existing index (primary or unique key) is used as clustered one then this query will return all columns which are used in this clustered index.
If there is no explicitly defined index which is used as clustered one, and synthetic 6-byte inner row number is used, then the query will return empty rowset.
After the columns list received you may find needed index in CREATE TABLE text - this index expression contains all found columns names (in any order), does not contain any other columns, operators, function calls and so on, and it is UNIQUE or PRIMARY KEY.
Also, if some column in the list is CHAR/VARCHAR/BINARY/VARBINARY column, then the index expression must include the whole column and not the column prefix. I.e. if a column is column VARCHAR(10) then the index may be (.., column, ..) or (.., column(10), ..) but not (.., column(8), ..).