Search code examples
mysqlzend-frameworkzend-dbpartitioning

How to manage huge DB tables to minimize fetching time in MySql

I have a huge Database table containing around 5 Million rows. Now retrieving records make the server slow in some cases. How can i manage the table now as it growing over the days.

I was thinking to make some archiving technique on yearly basis for example breakdown the complete tables into many small tables on yearly basis, but that cost me a lot of changes in coding. I have to change the complete structure of querying from database. So, most probably changes on most of the places in project.

What else i can do to reduce fetching time of records from database tables? Is there any other technique that i can adopt to avoid many changes in my code?

Thanks in advance

Solution

  • You can easily PARITION the table horizontally using PARITION BY RANGE in MySQL.

    Also if you have many columns in table then you can break that table into two or more tables by Vertical partitioning method.

    Also add proper indexes preferably clustered or covering indexes on tables and test queries for performance by using EXPLAIN.