0
I have a table that currently has 25 million records, due to this any query I do however simple it is becomes very slow.
I was researching about INDICES to improve search performance and found that it helps a lot in the matter of speed and this is great, however, I saw several comments talking about the negative impact that they can generate in the insertions and changes (slow down).
This table that I want to modify (create indices) suffers many insertions and sometimes with more than 100 thousand records at a time.
My question is this, this impact on insertion or alteration after creating an index really happens? I’m afraid of adjusting the question of queries, however, disturbing the other things
Good I suggest to raise a backup of the base in another instance, create the indexes and do a performance test. Anything we say here will be speculation without knowing the actual structure of the bank, queries, volume, etc... An alternative would be to create an index by a date field and always use date to limit queries, even if it is loading temporary tables, This helps a lot in most cases, mainly darlings filled with
where,group byandorder by. I don’t imagine that all queries do search in this 25 million records.– Ricardo Pontual
Possible duplicate of What are the advantages and disadvantages of using indexes in databases?
– Don't Panic