MySQL database with entries increasing by 1 million every month, how can I partition the database to keep a check on query time

Question

• I am a college undergrad working on a PHP and MySQL based inventory management system operating on a country-wide level. Its database size is projected to increase by about 1 million plus entries every month with current size of about 2 million.

• I need to prevent the exponential increase in query time which is currently ranges from 7-11 seconds for most modules.

• The thing is that the probability of accessing data entered in the last month is much higher as compared to any older data. So I believe partitioning of data on the basis of time of data entry should be able to keep the query time in check. So how can I achieve this.

• Specifically speaking I want to have a way to cache the last month's data so that every query searches for the product in the tables having recent data and should search rest of the data in case it is not found in the last 1 month's data.

Answer 1

If you want to use the partitioning functions of MySQL, have a look at this article.

That being said, there are a few restrictions when using partitions :

• you cant have indexes that are not in the partition key
• you loose some database portability as partitioning works quite differently with other databases.

You can also handle partitioning manually, by moving old records to an archive table at regular intervals. Of course, you will then have to also implements different code to read those archived records.

Also note that your query time seems quite long. I have worked with table much larger than 2 million records with much better access time.