Mysql is not using Index

MySQL & MariaDB Other MySQL® Questions
1

Hello Guys

I have a mysql 5.7 performing almost ( Full Table scan ) on the select below.

So, I have a question. If I have the indexes below why have I getting full table scan on table cp_rawcdr ?

Shoud I create composite Index with all columns ???

Ps ; I have already performed analyze in both tables and I can’t use hints on this query

  PRIMARY KEY (`id`),
  KEY `cp_rawcdr_network_id_47a232398250b37d_fk_cp_network_id` (`network_id`),
  KEY `cp_rawcdr_date_sent` (`date`,`sent`),
  KEY `cp_rawcdr_sim_card_id_sent` (`sim_card_id`,`sent`),

mysql> explain
    -> SELECT `cp_rawcdr`.`id` , `cp_rawcdr`.`sim_card_id`, `cp_rawcdr`.`date`, `cp_rawcdr`.`end_date`, `cp_rawcdr`.`duration`, `cp_rawcdr`.`network_id`, `cp_rawcdr`.`type`, `cp_rawcdr`.`sent`,
    -> `cp_rawcdr`.`external_correlation_id`, `cp_rawcdr`.`b_party_number`
    -> FROM `cp_rawcdr`
    -> WHERE ((`cp_rawcdr`.`sim_card_id`) IN (SELECT U0.`id` FROM `cp_simcard` U0 WHERE U0.`organization_id` = 4039) AND `cp_rawcdr`.`sent` = 0)
    -> Order By `cp_rawcdr`.`id`;
+----+-------------+-----------+------------+--------+--------------------------------------------------------------------------+---------+---------+------------------------------+-----------+----------+-------------+
| id | select_type | table     | partitions | type   | possible_keys                                                            | key     | key_len | ref                          | rows      | filtered | Extra       |
+----+-------------+-----------+------------+--------+--------------------------------------------------------------------------+---------+---------+------------------------------+-----------+----------+-------------+
|  1 | SIMPLE      | cp_rawcdr | NULL       | index  | cp_rawcdr_sim_card_id_sent                                               | PRIMARY | 4       | NULL                         | 672525897 |    10.00 | Using where |
|  1 | SIMPLE      | U0        | NULL       | eq_ref | PRIMARY,cp_simcar_organization_id_53ac94325583cf1e_fk_cp_organization_id | PRIMARY | 4       | trum2m.cp_rawcdr.sim_card_id |         1 |    39.26 | Using where |
+----+-------------+-----------+------------+--------+--------------------------------------------------------------------------+---------+---------+------------------------------+-----------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql>
2

Hello Andre,

To answer your question first, “cp_rawcdr” is not getting a full table scan. It is getting index scan as EXPLAIN shows in “keys” having PRIMARY.
The query is trying to fetch all the sim_card_id matching your subquery from cp_simcard. Question is how many records the subquery is returning? I believe the cardinality of organization_id is low resulting into many records getting returned.
You already have the index cp_rawcdr_sim_card_id_sent as a possible_keys though optimizer is preferring to read Primary index in sorted order as a better option than using your secondary index and then sorting it.

I’d try rewriting the subquery as join instead and explain again:

SELECT cp_rawcdr.id, cp_rawcdr.sim_card_id, cp_rawcdr.date, cp_rawcdr.end_date, cp_rawcdr.duration,  cp_rawcdr.network_id, cp_rawcdr.type, cp_rawcdr.sent, cp_rawcdr.external_correlation_id, cp_rawcdr.b_party_number
FROM cp_rawcdr JOIN cp_simcard U0 
ON cp_rawcdr.sim_card_id = U0.id AND U0.organization_id = 4039
WHERE cp_rawcdr.sent = 0
ORDER BY cp_rawcdr.id;

Thanks,
K

3

Hello

As mention before, my issue is table cp_rawcdr whenever I use ( cp_rawcdr .id , cp_rawcdr .sim_card_id , cp_rawcdr .date , cp_rawcdr .end_date , cp_rawcdr .duration , cp_rawcdr .network_id , cp_rawcdr .type , cp_rawcdr .sent ,
→ cp_rawcdr .external_correlation_id , cp_rawcdr .b_party_number ) . On the hand , If I use only ( cp_rawcdr .id ) We have good result time.

image
image965×607 28.9 KB

4

Right, so if you only fetch the ID, your query is covered with the primary index itself - but other fields are not covered by any other and hence it need to go to disk (or if available from bufferpool)!
Well based on this an index on all the columns (won’t include PK in that ofcourse) will be used to fetch the records directly from the index without needing additional diskseeks. But I’d surely think of write cost and disk size it will incur.

Thanks,
K.

5

Thank you for your analyze , It is that I thought . For a table with 680 millions I dont think as a good option to create Index covering all columns

6

You’re welcome Andre,
Did you consider adding additional filters to reduce the amount of records the query is processing? Adding a date range for example? Did you try introducing join? Just wondering.

1 Like
7

It depends of dev team because they are using python framework to generate this query.

I have just trying to improve this code and also Yes I have already implement It with joins , subquerys , however as we know If I use all columns happen Full table scan plan