MySQL does not use index, does full table scan instead

Question

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 ids 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.

Answer 1

If you really need the "&" operator, here are a couple of tips:

• Since you have INDEX(flags, date), DROP the redundant INDEX(flags). (I have found cases where the shorter index is picked even when the wider one would be better.)

• Find the ids, then find the rest of the columns. This can be especially efficient if the table is much bigger than RAM.

  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)
  

Provide the EXPLAIN for that query. I would expect the derived table (subquery) to say "Using index" meaning that it was performed entirely using the index's BTree, which is much smaller than the data's BTree. Hence faster. Then the JOIN does 250K lookups via the PK.

Regardless, 250K rows takes a long time.