MySQL index sign of decimal
Is there an efficient way to index the sign of a DECIM开发者_运维知识库AL column with MySQL (InnoDB)?
I've a query with the condition where amount > 0
(and visa versa) and I'm wondering if there's a fancy way to make it speedy. In the same way that indexing the first character of a string can speed up a query like select distinct left(some_string_column, 1)
.
Indexing the Decimal column will help MySQL to return result where column > 0
.
CREATE INDEX signindex ON table(deccolumn);
Besides, in order to allow MySQL to use indexes effectively, and avoid the use of a function in where
, you can add a new column that has the pre-calculated data, and index that new column
ALTER TABLE table ADD leftchar char(1);
UPDATE table SET leftchar=left(some_string_column, 1);
CREATE INDEX charindex ON table(leftchar);
SELECT DISTINCT leftchar FROM table;
精彩评论