MySQL index + math operators

optimization - MySQL index + math operators - Stack Overflow [原文更详细]

I have RatingTable:

UserID int, Rating int, BanMask int, index rating_index (Rating DESC), index ban_index (BanMask ASC)

Let's say there're over 5 million rows in this table and only ~100 really banned users. Will select query still be optimized if I use bit math operations on indexed field? Are this 2 queries will use index optimization?

SELECT * FROM ProfileTable WHERE BanMask > 0 ORDER BY Rating DESC LIMIT 10

vs

SELECT * FromProfileTable WHERE (BanMask & (1 << 2)) > 0 ORDER BY Rating DESC LIMIT 10

You can confirm for yourself which indexes are used for a given query by using EXPLAIN. You should study this manual page to get an explanation of the output: 

I expect no index can be used for your query using the expression：WHERE (BanMask & (1 << 2)) > 0

In general, an index cannot be used if your indexed column on the left side of the comparison operator is referenced in an expression or in a function. It must be a "bare" column. 通常，如果比较运算符左侧的索引列在表达式或函数中被引用，则不能使用索引。它必须是一个“裸”列。

Here's a workaround for BanMask > 0. Instead of having the BanMask having a bunch of different non-zero values, have a single value that says banned: WHERE banned = 1

MySQL会自动优化常量表达式1 << 2吗？写成4麻烦吗？个别培训班出来的是不是根本不用索引？能不能在买新服务器前先挖下潜力？AI都(号称)能编程了，不努力不行啊。