# Mysql is not using Index **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [May 14, 2023, 6:34pm UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044 "2023-05-14T18:34:36Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\ **Post date:** [May 14, 2023, 6:34pm UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/1 "2023-05-14T18:34:36Z")
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 ```auto 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`), ``` ```auto 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> ``` ---
**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\ **Post date:** [May 15, 2023, 3:59am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/2 "2023-05-15T03:59:41Z")
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: ```auto 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 ---
**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\ **Post date:** [May 15, 2023, 7:14am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/3 "2023-05-15T07:14:16Z")
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](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/b/b4d1fe26b5dbc973ae11c4248b6117b8952cee83.png) ---
**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\ **Post date:** [May 16, 2023, 5:08am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/4 "2023-05-16T05:08:18Z")
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. ---
**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\ **Post date:** [May 16, 2023, 5:51am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/5 "2023-05-16T05:51:56Z")
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 ---
**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\ **Post date:** [May 16, 2023, 6:12am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/6 "2023-05-16T06:12:16Z")
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. ---
**Author:** ![Andre1976](https://avatars.discourse-cdn.com/v4/letter/a/a587f6/32.png) [@Andre1976](https://forums.percona.com/u/Andre1976)\ **Post date:** [May 16, 2023, 6:39am UTC](https://forums.percona.com/t/mysql-is-not-using-index/22044/7 "2023-05-16T06:39:07Z")
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