EDIT: See further developments below.
I have an InnoDB table with the following schema:
CREATE TABLE `data` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`app_id` CHAR(27) NULL DEFAULT NULL,
`user_id` CHAR(27) NOT NULL COLLATE 'utf8mb4_0900_as_cs',
`level_id` INT NOT NULL,
`item_id` VARCHAR(50) NOT NULL,
`user_addr` VARCHAR(255) NOT NULL,
`owner_addr` VARCHAR(255) NOT NULL,
`reward` DECIMAL(10,5) UNSIGNED NOT NULL,
`date` DATE NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`item_addr` VARCHAR(255) NOT NULL,
`client_replay_id` VARCHAR(255) NOT NULL,
`processed_at` DATETIME NULL DEFAULT NULL,
`flags` INT UNSIGNED NOT NULL DEFAULT '0',
`owner_batch_id` CHAR(27) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_as_cs',
`user_batch_id` CHAR(27) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_as_cs',
PRIMARY KEY (`id`),
INDEX `item_date_idx` (`item_id`, `date`),
INDEX `owner_batch_id` (`owner_batch_id`),
INDEX `user_batch_id` (`user_batch_id`),
INDEX `flags` (`flags`),
INDEX `owner_idx` (`owner_addr`),
INDEX `level_reward_idx` (`date`, `item_id`, `app_id`, `level_id`),
INDEX `user_balance_idx` (`user_addr`, `item_id`, `flags`),
INDEX `owner_user_flags_idx` (`user_addr`, `owner_addr`, `flags`),
INDEX `flags_date_idx` (`flags`, `date`)
) COLLATE='utf8mb4_0900_ai_ci';
This table has ~14 million rows. I have a SELECT
query using the flags
and date
columns. Despite having a composite index specifically for those columns (flags_date_idx
) the query plan shows a full table scan instead:
mysql> EXPLAIN SELECT * FROM data WHERE flags&3=0 AND `date`>= '2022-06-19' AND `date` < '2022-07-04';
+----+-------------+-----------------+------------+------+------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+------------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | data | NULL | ALL | level_reward_idx | NULL | NULL | NULL | 14017708 | 50.00 | Using where |
+----+-------------+-----------------+------------+------+------------------+------+---------+------+----------+----------+-------------+
Index hints don't help either:
mysql> EXPLAIN SELECT * FROM data USE INDEX(flags_date_idx) WHERE flags&3=0 AND `date`>= '2022-06-19' AND `date` < '2022-07-04';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | data | NULL | ALL | NULL | NULL | NULL | NULL | 14018064 | 11.11 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0,02 sec)
As it stands, this query can take up to several minutes to return, depending on the date range. How can I avoid a full table scan here?
EDIT: The problem was the bitwise query flags&3=0
, which made it impossible to use any index because MySQL has to do a calculation for every row. Replacing it with flags NOT IN (1,2,3)
(the possible values for this column) resolved the issue. See comments, and these SO answers [1][2].
EDIT 2: As suggested by Rick James' answer, I tried grabbing the id
s first and then using them to retrieve the columns:
mysql> explain SELECT b.* FROM ( SELECT id FROM data WHERE flags&3=0 AND date>= '2022-06-19' AND date < '2022-07-04' ) AS ids JOIN data AS b USING(id);
+----+-------------+-----------------+------------+--------+--------------------------+----------------+---------+------------------------------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+--------------------------+----------------+---------+------------------------------+----------+----------+--------------------------+
| 1 | SIMPLE | data | NULL | index | PRIMARY,level_reward_idx | flags_date_idx | 7 | NULL | 14263666 | 50.00 | Using where; Using index |
| 1 | SIMPLE | b | NULL | eq_ref | PRIMARY | PRIMARY | 8 | data.id | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+--------+--------------------------+----------------+---------+------------------------------+----------+----------+--------------------------+
2 rows in set, 1 warning (0,04 sec)
Now it actually uses the correct index, but judging by rows
it still scans the whole table. I'm confused.
flags&3=0
returns ~250K rows out of 14 million.&3
is the problem here. As a test, tryflags=0
orflags IN ([series of integers where &3 yields 0])
and see if that helps. Or try creating a new column forflags&3
and index that.flags=0
andflags NOT IN (1,2,3)
both use the correct index. So, it seems MySQL simply doesn't support using indexes when querying with bitwise operators.date
depending on how selective that range is.