Mysql index is not working in some queries

Question

I have the below query in which the where condition uses edge_server column. I have defined the edge_server column as index, however the explain statement is showing that full table scan is performed without using the index, why is it so?

SELECT 
   SUM(`cre_kpi`.`attempts`) AS `attempts__sum`, 
   SUM(`cre_kpi`.`asr`) AS `asr__sum`, 
   SUM(`cre_kpi`.`ner`) AS `ner__sum`,
   SUM(`cre_kpi`.`cranckbackCount`) AS `cranckbackCount__sum` 
FROM `cre_kpi` 
WHERE (`cre_kpi`.`edge_server` like 'AFTEC1')

Does this answer your question? [Mysql - "Select like" not using index](https://stackoverflow.com/questions/12030028/mysql-select-like-not-using-index) — kmoser, Apr 21 '23 at 06:03
```WHERE (`cre_kpi`.`edge_server` like 'AFTEC1')``` is the same as ```WHERE (`cre_kpi`.`edge_server` = 'AFTEC1')``` - use equiation instead of LIKE. — Akina, Apr 21 '23 at 06:29
It would help if you would edit the question and paste the contents of the EXPLAIN rather than paraphrasing them. — Andy Lester, Apr 21 '23 at 14:58

score 2 · Answer 1 · answered Apr 21 '23 at 06:08

2

There can be many possibility, maybe it's because your data has too few distinct values, the optimizer decided a full table scan is more efficient than using index, or the index statistics are not up to date, the optimizer decided not to use the index etc.

Anyways, it's can be many reasons, you can use EXPLAIN statement to see how the optimizer is accessing the data and whether it's using the index: https://dev.mysql.com/doc/refman/8.0/en/using-explain.html.

answered Apr 21 '23 at 06:08

Bryce Chan

1,639
11
26

Thanks for the feedback. How can I identify the exact reason why the index is not used in a query? – Jibin Apr 21 '23 at 06:40
Did you try the ```EXPLAIN``` statement? For example ```EXPLAIN SELECT * FROM a WHERE b > 1;```. – Bryce Chan Apr 21 '23 at 06:48
Yes I tried the explain statement, but the index is not recogniced? – Jibin Apr 21 '23 at 07:30
1

The ```EXPLAIN``` statement should show you two columns ```type``` and ```Extra```. The ```type``` column has the information about the type of join being used e.g. index. And the ```Extra``` column has information about how the query is executed e.g. using temporary tables or a specific index etc. – Bryce Chan Apr 21 '23 at 07:34

score 1 · Answer 2 · answered Apr 21 '23 at 17:00

What percent of the table has edge_server like 'AFTEC1'? (Bryce alludes to this.) If more than about 20%, the Optimizer will decide to scan the table rather than bounce between the index's BTree and the data BTree.

EXPLAIN FORMAT=JSON may give clues as to why it picked doing a table scan. The Optimizer trace goes into more detail and "cost" analysis.

Mysql index is not working in some queries

2 Answers2