MySQL query performance on secondary index

Question

I have a issue about select query performance . My table DDL(gen from show create table test) as below:

CREATE TABLE `test` (
  `id` bigint NOT NULL,
  `issue_code` varchar(10) DEFAULT NULL,
  `issue` char(12) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_01` (`issue_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

db version is 8.0.23

I trigger below two query statements :

S1 : select issue_code from test WHERE issue_code = '1104' limit 200000 ;
S2 : select id from test WHERE issue_code = '1104' limit 200000 ;

The S2 is more slow than S1 ,but the id column is primary key and bundle on secondary index idx_01.

So I really don't know why S2 is so slowly than S1 ?

The execution plan are both the same.

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  test    NULL    ref idx_01  idx_01  43  const   224941  100.00  Using index .

show profiles :

Query_ID    Duration    Query
1   0.10767725  select issue_code from test WHERE issue_code = '1104' limit 200000
2   0.10759700  select issue_code from test WHERE issue_code = '1104' limit 200000
3   0.10683650  select issue_code from test WHERE issue_code = '1104' limit 200000
4   1.03925400  select id from test WHERE issue_code = '1104' limit 200000
5   1.08807700  select id from test WHERE issue_code = '1104' limit 200000
6   1.30657375  select id from test WHERE issue_code = '1104' limit 200000

Query 1 detail:

Status  Duration    CPU_user    CPU_system  Context_voluntary   Context_involuntary Block_ops_in    Block_ops_out   Messages_sent   Messages_received   Page_faults_major   Page_faults_minor   Swaps   Source_function Source_file Source_line
starting    0.000070    0.000067    0.000002    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000004    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000008    0.000001    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000006    0.000005    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000148    0.000144    0.000005    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000007    0.000006    0.000001    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000008    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000013    0.000013    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000080    0.000077    0.000003    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000037    0.000035    0.000001    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   0.106980    0.328047    0.000000    54  2   0   0   0   0   0   0   0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000006    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000014    0.000013    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000261    0.000262    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000010    0.000010    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

Query 4 detail:

Status  Duration    CPU_user    CPU_system  Context_voluntary   Context_involuntary Block_ops_in    Block_ops_out   Messages_sent   Messages_received   Page_faults_major   Page_faults_minor   Swaps   Source_function Source_file Source_line
starting    0.000076    0.000076    0.000000    0   0   0   0   0   0   0   0   0   NULL    NULL    NULL
Executing hook on transaction   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1378
starting    0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   launch_hook_trans_begin rpl_handler.cc  1380
checking permissions    0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   check_access    sql_authorization.cc    2272
Opening tables  0.000041    0.000041    0.000000    0   0   0   0   0   0   0   0   0   open_tables sql_base.cc 5780
init    0.000012    0.000012    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   603
System lock 0.000009    0.000008    0.000000    0   0   0   0   0   0   0   0   0   mysql_lock_tables   lock.cc 332
optimizing  0.000014    0.000014    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    334
statistics  0.000090    0.000090    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    603
preparing   0.000022    0.000022    0.000000    0   0   0   0   0   0   0   0   0   optimize    sql_optimizer.cc    687
executing   1.038229    2.064186    0.020909    1644    50  0   0   0   0   0   19  0   ExecuteIteratorQuery    sql_union.cc    1128
end 0.000016    0.000015    0.000000    0   0   0   0   0   0   0   0   0   execute sql_select.cc   636
query end   0.000005    0.000005    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6350
waiting for handler commit  0.000007    0.000007    0.000000    0   0   0   0   0   0   0   0   0   ha_commit_trans handler.cc  1700
closing tables  0.000009    0.000010    0.000000    0   0   0   0   0   0   0   0   0   mysql_execute_command_internal  sql_parse.cc    6421
freeing items   0.000695    0.000694    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_sql_command sql_parse.cc    6947
cleaning up 0.000009    0.000009    0.000000    0   0   0   0   0   0   0   0   0   finish_dispatch_command sql_parse.cc    3378

Why S2(Query 4~6) should need more context switch ?

Please edit your question to show (as text, not images) output of `show create table test` and `select version()` — ysth, Jun 22 '22 at 10:29
Can you please check if you are using myisam or innodb for your table (e.g. do as ysth suggestest and run and add `show create table test`) — Solarflare, Jun 22 '22 at 11:14
Were those 6 runs run sequentially? (10x smells like caching.) The result sets will be a different number of bytes; where did they go? /dev/null? To a screen and ignored? Other? What is the value of `innodb_buffer_pool_size`? — Rick James, Jun 22 '22 at 15:55
Hi @James, Yes ,I executed them sequentially and I use SequelPro to executed these statements. My buffer pool size is 16G . — linlowa, Jun 23 '22 at 09:51

DRapp · Answer 1 · 2022-06-23T12:29:51.120

The reason you are getting different results is mostly what is being returned. In your query where you are asking for the IssueCode where IssueCode = some value, the index being used already has all it needs without having to go back to the raw data pages for other column data. Since it is indexed it just grabs what it does and cuts off as soon as the limit is reached. Because you have no ORDER BY clause, it does not care about any ID ordinal basis, just grabs the issue code (which in this case is all the same value).

In the second, where you are asking for the ID where the issue code = some value, it needs to do a round-trip so-to-speak. It first needs the index to find out WHAT qualifies, THEN needs to go back to the raw data page pointed to by the index to get the ID component of said-qualified record.

Now, although your example has obviously been abbreviated of any significant private data, it also does not seem practical end-use. What you might be better is to have a composite or covering index based on your actual needs. In this case, having a COVERING index meets the WHERE condition but also carries along the other column(s) of interest thus preventing the need to go back to the raw data pages.

In this case, having an index on (issue_code, id) instead of relying on two individual indexes would give you better performance. Issue first to qualify the WHERE, but the ID is in there for the return column of interest. Now, it is not practical to do a covering index of say 4, 5 or more columns which would just bloat the index, but could be applicable to have a 3, 4 or 5 column-based index if you WHERE criteria warranted it for the filtering purposes.

Additionally, in combination of a WHERE and ORDER BY clause is also a good consideration to have the ORDER BY column as part of the index as well for performance. This way you get the WHERE qualified, but its also in order based on the final expected output.

FINAL CLARIFICATION.

The reason in query #2 does NOT utilize as a "covering" index is because your index is based only on the WHERE clause. You cant think of it as I have an index on each individually. That is now how the engine works. It analyzes the criteria and finds the best index for that. Loads that list of entries that qualify which is ONLY that of the issue code and does NOT have any context of the IDs associated. So it gets those INDEXED entries and THEN has to go back to the raw data to get the actual IDs to be returned.

A COVERING index is a SINGLE index that has BOTH components, hence (issue_code, id). So it can grab the WHERE criteria on the issue, AND at the same time, be able to return the ID without going to the raw data pages.

That is why the first query where you are only returning the issue code WHERE the issue_code is faster. It has the code and does not need anything else from the raw data. Does that help clarify?

Hi @DRapp, This question is real my case. I only want to get the id list. I still don't understand why MySQL not use convering index to query S2? — linlowa, Jun 23 '22 at 10:03

MySQL query performance on secondary index

1 Answers1