Why MYSQL Query dose not using the table’s composite index?

Note:

First I want to mention that I read all other related questions and answers.

Question:

I had created 2 indexes for the table I have but whenever I use my queries it’s not using the index. even when forced to use the index it’s not using it.

The table has 1.5M rows and it will be increased, and the query is taking 35+ seconds.

Query 1

    explain analyze 
    SELECT  sum(cid_user_usd_earned)
        FROM  `bbtv_adv_records`
        WHERE  (user_id =2
                  and  `cid_assign_month` = '2020-08-01'
                  And  `content_type` = 'UGC'
               );

    explain analyze 
    SELECT  sum(cid_user_usd_earned)
        FROM  `bbtv_adv_records`
          USE INDEX (bbtv_adv_records_user_id_cid_assign_month_content_type_index)
        WHERE  (user_id =2
                  and  `cid_assign_month` = '2020-08-01'
                  And  `content_type` = 'UGC'
               );

Query 2

    explain analyze 
    SELECT  *
        FROM  `bbtv_adv_records`
        WHERE  (user_id =2
                  and  `cid_assign_month` = '2020-08-01' 
               );

Query 3

    explain analyze 
    SELECT  sum(cid_user_usd_earned),channel_id
        FROM  `bbtv_adv_records`
        WHERE  (user_id =2
                  and  `cid_assign_month` = '2020-08-01'
                Group by  `channel_id`
               );

Table


CREATE TABLE `bbtv_adv_records` (
  `id` bigint UNSIGNED NOT NULL,
  `content_type` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 ....
  `channel_id` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 ...
  `cid_assign_month` date NOT NULL,
  `cid_process_state` enum('process','done','fail') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for table `bbtv_adv_records`
--
ALTER TABLE `bbtv_adv_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `bbtv_adv_records_user_id_cid_assign_month_index
          ` (`user_id`,`cid_assign_month`),
  ADD KEY `bbtv_adv_records_user_id_cid_assign_month_content_type_index`
            (`user_id`,`cid_assign_month`,`content_type`);



--
-- AUTO_INCREMENT for table `bbtv_adv_records`
--
ALTER TABLE `bbtv_adv_records`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1543191;
COMMIT;

mysql> show index from bbtv_adv_records

+------------------+------------+--------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table            | Non_unique | Key_name                                                     | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+------------------+------------+--------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bbtv_adv_records |          0 | PRIMARY                                                      |            1 | id               | A         |     1456578 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| bbtv_adv_records |          1 | bbtv_adv_records_user_id_cid_assign_month_index              |            1 | user_id          | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| bbtv_adv_records |          1 | bbtv_adv_records_user_id_cid_assign_month_index              |            2 | cid_assign_month | A         |          47 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| bbtv_adv_records |          1 | bbtv_adv_records_user_id_cid_assign_month_content_type_index |            1 | user_id          | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| bbtv_adv_records |          1 | bbtv_adv_records_user_id_cid_assign_month_content_type_index |            2 | cid_assign_month | A         |          49 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| bbtv_adv_records |          1 | bbtv_adv_records_user_id_cid_assign_month_content_type_index |            3 | content_type     | A         |          58 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+------------------+------------+--------------------------------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)

note the best result I have 10 seconds when creating only the cid_assign_month index alone, but all my queries have the cid_assign_month with the user_id always.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

could you show the result of “show index from bbtv_adv_records “.

The mysql query optimizer is a cost based optimiser.
It try to find out the best execution plan.
If the cost of ALL( full table scan) is less then REF_OR_NULL (using secondary index fetch data), It will use full table scan where time complexity is O(n) .

for example, there are the commonest cost matrix

  • the IO cost of Reading 1 block page is 1.
  • the CPU cost of comparing 1 record is 0.2.

the query 1:
SELECT sum(cid_user_usd_earned) FROM bbtv_adv_records WHERE (user_id =2 and cid_assign_month = ‘2020-08-01’ And content_type = ‘UGC’);

because the column cid_user_usd_earned is not in secondary index bbtv_adv_records_user_id_cid_assign_month_content_type_index, mysql will return to the primary index to get cid_user_usd_earned, after using binary search in secondary index.

Method 2

Solved

It looks that MySQL indexing will not work with varchar(400) or any large length.

change from

  `content_type` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `channel_id` varchar(400) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,

to

  `content_type` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `channel_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `user_id` bigint UNSIGNED DEFAULT NULL,

now the Query show indexing and faster from 34s to 8s

mysql> explain analyze SELECT sum(cid_user_usd_earned) FROM `bbtv_adv_records` WHERE (user_id =2 and `cid_assign_month` = '2020-08-01' And `content_type` = 'UGC');

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(bbtv_adv_records.cid_user_usd_earned)  (actual time=8247.975..8247.981 rows=1 loops=1)
    -> Index lookup on bbtv_adv_records using bbtv_adv_records_user_id_cid_assign_month_content_type_index (user_id=2, cid_assign_month=DATE'2020-08-01', content_type='UGC')  (cost=116370.56 rows=496622) (actual time=0.373..6175.144 rows=259373 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (8.25 sec)


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x