请问这种烂SQL怎么优化呀?

yangtingkun

如果NOT IN的值每次都会发生变化而且没有什么规律，你可能只有选择全表扫描了。

dinglj

索引能基于函数吗？

落日孤帆

不能吧

chensq

最初由 dinglj 发布
[B]索引能基于函数吗？ [/B]

A function-based index includes columns that are either transformed by a function
(for example, the UPPER function), or included in an expression (for example, col1
+ col2).
Defining a function-based index on the transformed column or expression allows
that data to be returned using the index when that function or expression is used in
a WHERE clause or an ORDER BY clause. Therefore, a function-based index can be
beneficial when frequently-executed SQL statements include transformed columns
(or columns in expressions) in a WHERE or ORDER BY clause.
Function-based indexes defined with the UPPER(column_name) or
LOWER(column_name) keywords allow case-insensitive searches. For example, the
following index:
CREATE INDEX uppercase_idx ON emp (UPPER(empname));
facilitates processing queries such as:
SELECT * FROM emp
WHERE UPPER(empname) = ’MARK’;

husthxd

最初由 violing 发布
[B]我再使用不等于或NOT IN,NOT EXISTS,还是用不到索引呀,而且NOT IN 的值有二十几个呢! [/B]

建立索引后分析过表和索引了吗?
表的数据量有多大?
列的选择性高不高?