MySQL Read lock and query performance analyses

Question

I've a table with the following structure.

CREATE TABLE `wallet_details` (
  `id` varchar(32) NOT NULL,
  `status` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`(3))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It contains around 30 Million rows.

I need to run the following query on it:

select * from my_table where `status` = "UNASSIGNED" limit 1 for update

Following is the explain output of it:

mysql> explain select * from my_table where `status` = "UNASSIGNED" limit 1 for update;
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
| id | select_type | table          | partitions | type | possible_keys | key        | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | my_table       | NULL       | ref  | idx_status    | idx_status | 11      | const | 12898668 |   100.00 | Using where |
+----+-------------+----------------+------------+------+---------------+------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.09 sec)

The above query is not scaling very much and I wanted to understand how the above works, at which stage is the query behaving undesirably/heavily, and how can I improve its performance.

Few of my questions are:

1. Would the query run through all of the 12 million rows before fetching the final row (see query has limit 1 condition)?
2. On how many rows would the read lock be on?

Answer 1

• Don't use "prefix indexes" (status(3)); they usually hurt more than they help.
• Shrink the table size...
• What kind of values are in id VARCHAR(32). (It is unusually to have such a bulky PRIMARY KEY.)
• Change status from a multi-byte varchar to a 1-byte ENUM.
• If there are other columns or indexes in the table, we need to see them in order to understand whether there are side issues.

• What is the value of innodb_buffer_pool_size? How much RAM do you have?

• The query will (based on the EXPLAIN) scan through the table until it finds the first entry with "UNASSIGNED". Without knowing the distribution of the data, I can't say when that would occur.

• Eliminating the prefixing may speed things up.
• Saying SELECT id instead of SELECT * may speed things up.
• (I don't know how whether the rows before the first "UNASSIGNED" will be locked.)