add int value of float to increase selecting speed

问题: If I have a large table with floating numbers, can it help in reading speed if I add a column that represent the int value of each float? maybe if the int value will be an...

问题:

If I have a large table with floating numbers, can it help in reading speed if I add a column that represent the int value of each float? maybe if the int value will be an index, then when I need to select all the floats that starts with certain int it will "filter" the values that are surely not necessary?

For example if there are 10,000 numbers, 5000 of which begin with 14: 14.232, 14.666, etc, is there an sql statement that can increase the selecting speed if I add the int value column?

  id  |  number   | int_value |
   1  |  11.232   |     11    |
   2  |  30.114   |     30    |
   3  |  14.888   |     14    |
  ..  |    ..     |     ..    |
 3005 |  14.332   |     14    |

回答1:

You can create a non clustered index on number column itself. and when selecting the data from table you can filtered out with like operator. No need of additional column,

Select * from mytable where number like '14%'


回答2:

First of all: Do you have performance issues? If not then why worry?

Then: You need to store decimals, but you are sometimes only interested in the integer part. Yes?

So you have one or more queries of the type

where number >= 14 and number < 15

or

where truncate(number, 0) = 14

Do you already have indexes on the number? E.g.

create index idx on mytable(number);

The first mentioned WHERE clause would probably benefit from it. The second doesn't, because when you invoke a function on the column, the DBMS doesn't see the relation to the index anymore. This shows it can make a difference how you write the query.

If the first WHERE clause is still too slow in spite of the index, you can create a computed column (ALTER TABLE mytable ADD numint int GENERATED ALWAYS AS truncate(number, 0) STORED), index that, and access it instead of the number column in your query. But I doubt that would speed things up noticeably.

As to your example:

if there are 10,000 numbers, 5000 of which begin with 14

This is not called a large table, but a small one. And as you'd want half of the records anyway, the DBMS would simply read all records sequentially and look at the number. It doesn't make a difference whether it looks at an integer or a decimal number. (Well, some nanoseconds maybe, but nothing you would notice.)

  • 发表于 2019-01-09 22:36
  • 阅读 ( 173 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除