1

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.

8
  • How limiting is the flag column? In other words, how many rows are returned if you remove the date conditions from the where clause. Also, you have several similar indexes. Try consolidating several of those. Commented Jul 3, 2022 at 12:46
  • It's pretty limiting. flags&3=0 returns ~250K rows out of 14 million. Commented Jul 3, 2022 at 12:47
  • 1
    Guessing the &3 is the problem here. As a test, try flags=0 or flags IN ([series of integers where &3 yields 0]) and see if that helps. Or try creating a new column for flags&3 and index that. Commented Jul 3, 2022 at 13:00
  • @barrycarter you're right! flags=0 and flags 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. Commented Jul 3, 2022 at 15:04
  • @DuruCanCelasun Think of an index as just an ordered list of the values, how would you effectively search that ordered list to get all the values which when bitwise anded with 3 give you 0? You would have to have an index on this expression instead for that to work, but you might as well just have a column per thing you are flagging, and index the ones that you would need to filter on (and would provide enough selectivity to be useful). You might be okay with just an index on date depending on how selective that range is. Commented Jul 3, 2022 at 15:11

1 Answer 1

0

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.

3
  • Subquery was a good idea! I added the results to the OP. Now it uses the index, but still seems to scan the entire table (rows = ~14M). Any idea why? Commented Jul 3, 2022 at 20:25
  • See if INDEX(date, flags) is any faster. The Optimizer has no technique for avoiding checking all flags. Bitwise operations are not "sargeable".
    – Rick James
    Commented Jul 3, 2022 at 22:26
  • Right, putting date first would probably reduce the rows that need to be tested against the bitwise condition. Still, it won't be as fast as replacing the bitwise check with flags NOT IN (...). Commented Jul 3, 2022 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.