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:

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:

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:

category_flag & 0x0110

matched when either 0x0100 or 0x0010 was present, effectively representing an OR condition.

• Rewriting it as:

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