Question 20007 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql index is not working in some queries

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')
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search