# QAN: unused index columns and full tbl scan columns are always empty

**URL:** <https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698>\
**Category:** PMM 3.x\
**Created:** [April 22, 2025, 4:38pm UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698 "2025-04-22T16:38:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 22, 2025, 4:38pm UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/1 "2025-04-22T16:38:07Z")

</div>

Hi Team,

Full table or unused index columns are always zero in the QAN dashboard.  
Can someone help me with the below? Why are we not able to see the below unused index or full table columns data?

```auto
**m_full_scan_cnt!=0 or m_full_scan_sum !=0 or m_no_index_used_cnt !=0 or m_no_index_used_sum !=0 or m_no_good_index_used_cnt !=0 or**
 **m_no_good_index_used_sum != 0**

```

PMM server version: 3.1.0  
MariaDB version: 10.6  
Query Source: slowlog

```auto
SELECT
    any(node_name) as Node_name,
    any(service_name) as App_Name,
    any(schema) as Schema,
    any(username) as Service_account,
    fingerprint Query,
    any(example) Sample_Query,
    sum(m_full_scan_sum)/sum(m_full_scan_cnt)*1000 as Full_TBL_or_Unused_index_Score,
    sum(m_query_time_sum)/sum(m_query_time_cnt)*1000 as Latency_ms,
    sum(num_queries)/($ __to-$__ from)*1000 QPS,
    sum(m_rows_examined_sum)/sum(m_rows_sent_sum) as Rows_exam_per_sent,
    sum(m_rows_examined_sum)/($ __to-$__ from)*1000 as Rows_examined,
    sum(m_rows_affected_sum)/($ __to-$__ from)*1000 as Rows_affected
FROM pmm."metrics"
WHERE $__timeFilter(period_start) and (m_full_scan_cnt!=0 or m_full_scan_sum !=0 or m_no_index_used_cnt !=0 or m_no_index_used_sum !=0 or m_no_good_index_used_cnt !=0 or 
m_no_good_index_used_sum != 0)
GROUP BY fingerprint
ORDER BY (Full_TBL_or_Unused_index_Score) desc limit 10;

```

---

<div class="post-metadata">

**Author:** ![nurlan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/nurlan/32/1523_2.png) [@nurlan](https://forums.percona.com/u/nurlan)\
**Post date:** [April 23, 2025, 8:12am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/2 "2025-04-23T08:12:27Z")

</div>

Can you share your slowlog file? We need to test it.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 23, 2025, 3:34pm UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/3 "2025-04-23T15:34:42Z")

</div>

Hi @nurlan

I am sharing the details, which are that the query does not have any index on the column and is scanning the entire table.

Here the below select query is scanning the entire table, but we are not seeing that information in the logs as well.

CREATE TABLE `tt_ent_log_tbl` (  
`tt_ent_log_asoctn_id` bigint(20) NOT NULL AUTO\_INCREMENT COMMENT ‘Surrogate key’,  
`tt_ent_log_id` bigint(20) NOT NULL COMMENT ‘OT Event Log Id’,  
`entity_type` varchar(20) DEFAULT NULL COMMENT ‘The type of the entity for which the event occured’,  
`entity_id` bigint(20) DEFAULT NULL COMMENT ‘The id of the object for which the event occured’,  
`status` char(1) NOT NULL DEFAULT ‘A’ COMMENT ‘Value is “A” or “I”, to indicate status is Active or Inactive’,  
`crt_user` varchar(255) NOT NULL COMMENT ‘Creation User’,  
`crt_dt` datetime NOT NULL DEFAULT current\_timestamp() COMMENT ‘Creation Timestamp’,  
`last_upd_user` varchar(255) NOT NULL COMMENT ‘Last Update User’,  
`last_upd_dt` datetime NOT NULL DEFAULT current\_timestamp() ON UPDATE current\_timestamp() COMMENT ‘Last Update Timestamp’,  
`source_sys` varchar(255) DEFAULT NULL COMMENT ‘Source from which record came from’,  
PRIMARY KEY (`tt_ent_log_asoctn_id`),  
KEY `idx_tt_ent_log_assostn_02` (`tt_ent_log_id`),  
KEY `idx_tt_ent_log_assostn_01` (`entity_id`,`entity_type`)  
) ENGINE=InnoDB;

MariaDB [tds]\> select \* from tt\_ent\_log\_tbl where status=‘L’;  
Empty set (1 min 27.678 sec)

MariaDB [tds]\> explain select \* from tt\_ent\_log\_tbl where status=‘L’;  
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+  
| 1 | SIMPLE | ot\_event\_log\_associations | ALL | NULL | NULL | NULL | NULL | 30971587 | Using where |  
±-----±------------±--------------------------±-----±--------------±-----±--------±-----±---------±------------+  
1 row in set (0.000 sec)

MariaDB [tds]\> select count(1) from tt\_ent\_log\_tbl;  
±---------+  
| count(1) |  
±---------+  
| 37709813 |  
±---------+  
1 row in set (27.457 sec)

SET timestamp=1745419812;  
set autocommit=1;

# User@Host: ts\_ap\_usr[ts\_ap\_us] @ [10.6.12.142]

# Thread\_id: 1155096 Schema: tds QC\_hit: No

# Query\_time: 0.000417 Lock\_time: 0.000156 Rows\_sent: 1 Rows\_examined: 2

# Rows\_affected: 0 Bytes\_sent: 1377

SET timestamp=1745419812;  
select v1\_0.addr\_id,v1\_0.addr\_detail,v1\_0.addr\_line\_1,v1\_0.addr\_line\_2,v1\_0.addr\_line\_3,v1\_0.cas\_loc\_key,v1\_0.city,v1\_0.cntry\_cd,v1\_0.comm\_id,v1\_0.comm\_type\_c  
d,v1\_0.county,v1\_0.crt\_dt,v1\_0.crt\_user,v1\_0.last\_upd\_dt,v1\_0.last\_upd\_user,v1\_0.postal\_cd,v1\_0.postal\_cd2,v1\_0.source\_sys,v1\_0.state\_cd,v1\_0.status from addr  
esses v1\_0 where v1\_0.comm\_id=26135451 and v1\_0.status=‘A’ order by v1\_0.last\_upd\_dt desc;

# Time: 250423 7:48:51

# User@Host: tpd[temp\_usr] @ localhost

# Thread\_id: 1158802 Schema: tds QC\_hit: No

# Query\_time: 81.177402 Lock\_time: 0.000183 Rows\_sent: 0 Rows\_examined: **37709366**

# Rows\_affected: 0 Bytes\_sent: 1046

SET timestamp=1745419731;  
**select \* from tt\_ent\_log\_tbl where status=‘L’;**

# Time: 250423 7:50:12

# User@Host: vh\_ap\_ur[vh\_ap\_ur] @ [10.1.15.160]

# Thread\_id: 1159971 Schema: tds QC\_hit: No

# Query\_time: 0.000603 Lock\_time: 0.000395 Rows\_sent: 8 Rows\_examined: 8

# Rows\_affected: 0 Bytes\_sent: 807

SET timestamp=1745419812;  
DESCRIBE `location_types`;

# User@Host: ts\_ap\_ur[ts\_ap\_ur] @ [10.18.92.142]

# Thread\_id: 1155096 Schema: tds QC\_hit: No

# Query\_time: 0.000276 Lock\_time: 0.000114 Rows\_sent: 1 Rows\_examined: 2

# Rows\_affected: 0 Bytes\_sent: 982

SET timestamp=1745419812;  
select v1\_0.vendor\_pref\_id,v1\_0.crt\_dt,v1\_0.crt\_user,v1\_0.facility\_id,v1\_0.last\_upd\_dt,v1\_0.last\_upd\_user,v1\_0.pref\_cat\_cd,v1\_0.pref\_cd,v1\_0.pref\_id,v1\_0.source\_sys,v1\_0.status,v1\_0.vendor\_id from vend\_prnces v1\_0 where v1\_0.vendor\_id=24093 and v1\_0.pref\_id!=0 and v1\_0.pref\_cat\_cd=‘PH\_USG’ and v1\_0.status=‘A’ and v1\_0.pref\_cd=‘PPH’ order by v1\_0.last\_upd\_dt desc;

# User@Host: tm\_ap\_usr[tm\_ap\_usr] @ [10.18.12.142]

# Thread\_id: 1155096 Schema: tds QC\_hit: No

# Query\_time: 0.000286 Lock\_time: 0.000119 Rows\_sent: 1 Rows\_examined: 2

# Rows\_affected: 0 Bytes\_sent: 922

SET timestamp=1745419812;  
select v1\_0.phone\_num\_id,v1\_0.cas\_loc\_key,v1\_0.comm\_id,v1\_0.comm\_type\_cd,v1\_0.crt\_dt,v1\_0.crt\_user,v1\_0.last\_upd\_dt,v1\_0.last\_upd\_user,v1\_0.phone\_ext,v1\_0.phone\_num,v1\_0.source\_sys,v1\_0.status from ph\_ners v1\_0 where v1\_0.comm\_id=26135457 and v1\_0.status=‘A’ order by v1\_0.last\_upd\_dt desc;

# User@Host: tm\_ap\_ur[tm\_ap\_ur] @ [10.14.12.142]

# Thread\_id: 1155096 Schema: tds QC\_hit: No

:

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 24, 2025, 7:06am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/4 "2025-04-24T07:06:01Z")

</div>

@nurlan Can you please help me with the issue?

---

<div class="post-metadata">

**Author:** ![nurlan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/nurlan/32/1523_2.png) [@nurlan](https://forums.percona.com/u/nurlan)\
**Post date:** [April 24, 2025, 11:27am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/5 "2025-04-24T11:27:25Z")

</div>

Hi, would be good if you could put into code block.

---

<div class="post-metadata">

**Author:** ![nurlan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/nurlan/32/1523_2.png) [@nurlan](https://forums.percona.com/u/nurlan)\
**Post date:** [April 24, 2025, 11:37am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/6 "2025-04-24T11:37:54Z")

</div>

Hi @Naresh9999,  
So far I see that your slowlog output doesn’t have info about full\_scan and no\_good\_index\_used and that’s why there are no information about that in QAN.

Please check if slow log verbosity has correct value [Slow Query Log Extended Statistics - MariaDB Knowledge Base](https://mariadb.com/kb/en/slow-query-log-extended-statistics/)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 24, 2025, 11:41am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/7 "2025-04-24T11:41:24Z")

</div>

Sure, @nurlan

I will check and let you know the details.

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 24, 2025, 11:52am UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/8 "2025-04-24T11:52:40Z")

</div>

Hi @nurlan

It’s showing empty. I have set the value to full, and now I am able to see full scan details for the above query.

Thank you so much, @nurlan, for the help.

MariaDB [(none)]\> show global variables like ‘%log\_slow\_verbosity%’;  
±-------------------±------+  
| Variable\_name | Value |  
±-------------------±------+  
| log\_slow\_verbosity | |  
±-------------------±------+  
1 row in set (0.001 sec)

---

<div class="post-metadata">

**Author:** ![Naresh9999](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/naresh9999/32/5568_2.png) [@Naresh9999](https://forums.percona.com/u/Naresh9999)\
**Post date:** [April 28, 2025, 6:13pm UTC](https://forums.percona.com/t/qan-unused-index-columns-and-full-tbl-scan-columns-are-always-empty/37698/9 "2025-04-28T18:13:55Z")

</div>

@nurlan After enabling the log\_slow\_verbosity to all, I can see all of the information, but here I don’t see unused index-related information.

 ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/3/53cf3935a3e3396ffab845812d1137fb54aac4d9.png)