Mysql - What is the best input to be created in this case

Asked

Viewed 76 times

0

Hello,

I have a table in the database with the product price history. This table is very voluminous having more than 1 billion records.

How can I create an index in this table, to meet the query below:

select 
    cd_produto_price,
    min(vlr_produto) as vlr_produto 
from 
    preco_produto_fornecedor 
where 
    dt_atualizacao < date(now()) 
and vlr_produto > 0 
group by 
    cd_produto_price;

Possible indices:

  1. Only the dt_update field
  2. Only the vlr_product field
  3. Fields dt_update, vlr_product
  4. Fields dt_update, vlr_product and cd_product
  5. Some other option?

Note: I need to create the right Intel, because the base is already very large (disk space)

  • 1

    It is difficult to be sure without knowing the probable data that will be on the table. What may look good in theory may not be in practice. http://answall.com/q/32052/101. In many cases the column used to group is a good idea. But depending on how it will interact with the filter can get complicated. At first I would be between some variation of item 4. The best way to know what is the best index is by creating them and testing. This is true for each current database, version and data standard.If you create too much index you may have problems:http://answall.com/q/23348/101

  • 1

    I think the case of rethinking architecture. In this volume of data it is not very normal to work on the history table, but to create an intermediate aggregation.

  • ola Bacco.. I already own another table where I keep the most current price information. however, I have a history feature, where the user can follow all the prices of the last 30 days.. It is 1 price per week if the deadline is more than 30 days. I already delete the records that do not matter.

  • bigown, I had an Index only by column value (item 2), but besides this had several other indices and the bank size was no longer fitting in the HD. I eliminated all, I kept the main Intel and it’s all right. Only Dice missed to answer this query where the main Intel, does not answer. send the type of each field help on something?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.