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.
