开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜