A strategy for storing information in a database
HI
We have a PHP application that stores big quantities of information in some tables, we are considering a couple of solutions in order to improve performance during queries, we want your opinion and suggestions about it:
1. Create a backup table(s) with the old records stored in there, the most important and frequent queries will be used only against the original table.
2. the same solution in previous point but in a different database.
Thanks in advance.
We have a PHP application that stores big quantities of information in some tables, we are considering a couple of solutions in order to improve performance during queries, we want your opinion and suggestions about it:
1. Create a backup table(s) with the old records stored in there, the most important and frequent queries will be used only against the original table.
2. the same solution in previous point but in a different database.
Thanks in advance.
Ray Paseur
Interesting question! Please post the CREATE TABLE scripts so we can see what the tables contain. Often we can suggest a better design if we know the access patterns to the data. I'm going to assume that you've got sufficiently powerful servers and you have had a DBA look at the indexing scheme, consider the EXPLAIN SELECT output, etc (and if you have not done that yet, it's worth a try).
If the end goal is the data is still on the same server then it doesn't really matter.
A better solution would be to backup the data to a new db and also archive the data seperately away from the server.
A better solution would be to backup the data to a new db and also archive the data seperately away from the server.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ray Paseur:
I apologize for responding so late.
Well, the development department told me that they are restructuring the database, anyway I will explain the new design:
The system is dedicated to sales and manufacturing, so each Order will have a document called Order Production that will describe each Product of each Order with two characteristics, A) The materials to creat it and B) The steps during manufacturing.
The idea above shows that the registers in the database will be multiplied more less like this:
Orders x Order Production x Products x Materials x Steps of Manufacturing
Situation that will generate a lot of information in those tables.
I apologize for responding so late.
Well, the development department told me that they are restructuring the database, anyway I will explain the new design:
The system is dedicated to sales and manufacturing, so each Order will have a document called Order Production that will describe each Product of each Order with two characteristics, A) The materials to creat it and B) The steps during manufacturing.
The idea above shows that the registers in the database will be multiplied more less like this:
Orders x Order Production x Products x Materials x Steps of Manufacturing
Situation that will generate a lot of information in those tables.
Would still be helpful to see the CREATE TABLE statements, and if you can give us some idea of the number of columns and rows in each table, we may be able to make some "back-of-the-envelope" estimates.