Slow Query Resolution: Optimizing Bit
LINE VOOM’s post server experienced intermittent timeouts when loading profiles belonging to users with hundreds of thousands of posts. The root cause was bitwise filtering on `category_flag` and `access_flag`, which prevented MySQL from efficiently using indexes and forced scans of all posts for a user. The team resolved the issue with MySQL 8.0.13 functional indexes and by changing the query predicates to exact decimal comparisons, reducing scanned rows from 805 to 31 in testing. ## The Slow Query and Its Root Cause - Post metadata was distributed across shards and partitioned tables. - `category_flag` and `access_flag` were stored as `bit(64)` values containing multiple status flags. - The problematic query filtered by: - `user_id` - `category_flag & 0x0100` - `access_flag & 0x0001` - For heavy users, the query scanned hundreds of thousands of posts and ran for more than 30 seconds. - Bitwise expressions operated on computed results rather than raw column values, preventing normal indexes from filtering efficiently. ## Choosing Functional Indexes - The team considered hardware upgrades, caching, and additional partitioning, but none addressed the root cause adequately. - MySQL 8.0.13 functional indexes could index expression results without changing the table schema. - The proposed composite index was: ```sql ALTER TABLE post_metadata ADD INDEX idx_user_premium_searchable ( user_id, (category_flag & 0x0100), (access_flag & 0x0001) ); ``` - Functional indexes rely on the query expression matching the index definition precisely. ## Discovering the Required Query Form - Initial attempts failed to use the index: - Truthy checks such as `category_flag & 0x0100` - Comparisons using `> 0` - Equality against hexadecimal values such as `= 0x0100` - The successful form used decimal equality: ```sql WHERE user_id = '{user_id}' AND (category_flag & 0x0100) = 256 AND (access_flag & 0x0001) = 1 ``` - In testing, scanned rows dropped from 805 to 31. - Index storage increased by approximately 24%, but the DBA team determined that production capacity was sufficient. ## Rolling Out the Indexes in Production - Indexes were created before changing the application queries. - The team used online schema changes to avoid service downtime and support pausing or rollback during replication problems. - Because dozens of tables across multiple shards were affected: - One shard was handled first for validation. - Only one or two tables were processed per day. - Work was avoided during periods when emergency DBA support was unavailable. - Index creation increased replication lag, causing newly created posts to temporarily disappear from read replicas. - The team reduced the cache expiration time for the affected post lists and accepted the remaining replication delay before resuming the rollout. ## Gradual Query Deployment and a Bitwise Logic Bug - Query changes were deployed gradually through a dynamic configuration system. - Each query pattern was tested on one shard before being expanded to the remaining shards. - This allowed changes to be rolled back immediately through configuration. - During rollout, a serious visibility bug was found. - The original condition: ```sql category_flag & 0x0110 ``` matched when either `0x0100` or `0x0010` was present, effectively representing an OR condition. - Rewriting it as: ```sql (category_flag & 0x0110) = 272 ``` required both bits to be set, creating an AND condition. - Because production data stored only the premium bit, some profiles returned no content. - The incident highlighted the need to verify the semantic meaning of bit flags before converting bitwise predicates into equality comparisons. ## Practical Recommendation For slow queries involving bit flags, consider functional indexes when using MySQL 8.0.13 or later. Ensure the query expression exactly matches the index definition, validate bitwise logic carefully, and use staged schema and query rollouts with monitoring and fast rollback mechanisms.
Read original(opens in new tab)