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]