A few days ago one of our intern @mydbops reached me with a SQL query. The query scans only a row according to the execution plan. But query does not seems optimally performing.
Below is the SQL query and its explain plan. ( MySQL 5.7 )
select username, role from user_roles where username= '9977223389' ORDER BY role_id DESC LIMIT 1;
Execution plan and table structure
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_roles
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 1
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
show create table user_roles\G
*************************** 1. row ***************************
Table: …[Read more]