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))

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?
  • 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.)
  • You didn't say anything about the last 2 questions I asked. Any thoughts on them? – Pankaj Singhal Sep 30 '17 at 13:38
  • I added some on. At least get rid of the prefiixing and *. – Rick James Sep 30 '17 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.