Description:
have the following table
master [localhost:22031] {msandbox} (sysbench) > show create table sbtest1\G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
As you can see it has a primary key in column "id" and an index on column "k". I executed queries against both queries and as it was expected those were not reported as NO_INDEX_USED. However when I did a union between both tables I got it flagged as NO_INDEX_USED
mysql> select t.processlist_id,esh.thread_id,esh.event_name,left(esh.sql_text,80) as sql_text,esh.rows_affected,esh.rows_sent,esh.rows_examined,esh.NO_INDEX_USED from performance_schema.events_statements_history esh inner join performance_schema.threads t on esh.thread_id = t.thread_id where t.processlist_id=2 order by esh.event_id;
+----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+
| processlist_id | thread_id | event_name | sql_text | rows_affected | rows_sent | rows_examined | NO_INDEX_USED |
+----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+
| 2 | 39 | statement/sql/select | select id,k from sbtest1 where id = 1 | 0 | 1 | 1 | 0 |
| 2 | 39 | statement/sql/select | select id,k from sbtest1 where k = 2521302 | 0 | 95 | 95 | 0 |
| 2 | 39 | statement/sql/select | select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2 | 0 | 96 | 192 | 1 |
+----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+
10 rows in set (0.00 sec)
If we look at the explain plan we can see that the select_type "UNION RESULT" is considered as not using index.
mysql> explain
-> select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2521302;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNION | sbtest1 | NULL | ref | k_1 | k_1 | 4 | const | 95 | 100.00 | Using index |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
Having said that, I think it is fair to assume that your query is using indexes and that it is flagged as NO_INDEX_USED for the UNION, which should be a bug.
How to repeat:
You may use the same steps mentioned above to reproduce. It should be quite simple to reproduce.
Create table for test:
create database sysbench;
use sysbench;
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
);
Populate some data inside of the table:
INSERT into sbtest1 values (1,2521302,"test1","test1");
INSERT into sbtest1 values (2,2521301,"test12","test2");
INSERT into sbtest1 values (4,2541301,"test111","test11");
and run next queries:
select id,k from sbtest1 where id = 1 ;
select id,k from sbtest1 where k = 2521302;
select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2;
Verify odd behavior with:
select esh.thread_id,esh.event_name,left(esh.sql_text,80) as sql_text,esh.rows_affected,esh.rows_sent,esh.rows_examined,esh.NO_INDEX_USED from performance_schema.events_statements_history esh where sql_text like "select id,k from sysbench.sbtest1%";
Description: have the following table master [localhost:22031] {msandbox} (sysbench) > show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) As you can see it has a primary key in column "id" and an index on column "k". I executed queries against both queries and as it was expected those were not reported as NO_INDEX_USED. However when I did a union between both tables I got it flagged as NO_INDEX_USED mysql> select t.processlist_id,esh.thread_id,esh.event_name,left(esh.sql_text,80) as sql_text,esh.rows_affected,esh.rows_sent,esh.rows_examined,esh.NO_INDEX_USED from performance_schema.events_statements_history esh inner join performance_schema.threads t on esh.thread_id = t.thread_id where t.processlist_id=2 order by esh.event_id; +----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+ | processlist_id | thread_id | event_name | sql_text | rows_affected | rows_sent | rows_examined | NO_INDEX_USED | +----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+ | 2 | 39 | statement/sql/select | select id,k from sbtest1 where id = 1 | 0 | 1 | 1 | 0 | | 2 | 39 | statement/sql/select | select id,k from sbtest1 where k = 2521302 | 0 | 95 | 95 | 0 | | 2 | 39 | statement/sql/select | select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2 | 0 | 96 | 192 | 1 | +----------------+-----------+---------------------------------+----------------------------------------------------------------------------------+---------------+-----------+---------------+---------------+ 10 rows in set (0.00 sec) If we look at the explain plan we can see that the select_type "UNION RESULT" is considered as not using index. mysql> explain -> select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2521302; +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ | 1 | PRIMARY | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | UNION | sbtest1 | NULL | ref | k_1 | k_1 | 4 | const | 95 | 100.00 | Using index | | NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+ Having said that, I think it is fair to assume that your query is using indexes and that it is flagged as NO_INDEX_USED for the UNION, which should be a bug. How to repeat: You may use the same steps mentioned above to reproduce. It should be quite simple to reproduce. Create table for test: create database sysbench; use sysbench; CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ); Populate some data inside of the table: INSERT into sbtest1 values (1,2521302,"test1","test1"); INSERT into sbtest1 values (2,2521301,"test12","test2"); INSERT into sbtest1 values (4,2541301,"test111","test11"); and run next queries: select id,k from sbtest1 where id = 1 ; select id,k from sbtest1 where k = 2521302; select id,k from sbtest1 where id = 1 union select id,k from sbtest1 where k = 2; Verify odd behavior with: select esh.thread_id,esh.event_name,left(esh.sql_text,80) as sql_text,esh.rows_affected,esh.rows_sent,esh.rows_examined,esh.NO_INDEX_USED from performance_schema.events_statements_history esh where sql_text like "select id,k from sysbench.sbtest1%";