Solved

A strategy for storing information in a database

Posted on 2014-12-03
6
69 Views
Last Modified: 2015-01-12
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.
0
Comment
Question by:dimensionav
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40479809
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).
0
 
LVL 58

Expert Comment

by:Gary
ID: 40479823
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.
0
 
LVL 14

Accepted Solution

by:
Hamidreza Vakilian earned 500 total points
ID: 40483606
I assume that you are going to perform both approaches on the same server.

The most important thing you have to notice, is which storage engine you are going to choose for archiving. Consider using ARCHIVE table if you know you barely query your archive table in the future. ARCHIVE tables use less disk space (it's compressed) but at the price of no indexing. So your queries, each of them will need a full table scan.

If your main table is INNODB, You may think of creating another INNODB table for archiving (disk space won't be reduced this way) . As you know INNODB supports indexing, spliting an INNODB into 2 tables may not significantly affect your query time. That all depends on the number of records in your main table. Is it hundreds, thousands or millions of rows there?

If you know that you are going to frequently query your archived table in the future, maybe the better way is to forget about ARCHIVING, if your record count is not huge.(e.g less than 1 billion).

You need an astute database consult on this to make sure you are taking the best possible solution.

I suggest you to read this article:
http://www.mysqltutorial.org/understand-mysql-table-types-innodb-myisam.aspx

I can help you more if you need more assistance.

Regards
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Assisted Solution

by:Hamidreza Vakilian
Hamidreza Vakilian earned 500 total points
ID: 40483652
You should also consider how many inserts you will perform every day and how often you are planning to archive the main table? Have you thought of locking the table at the time of moving rows from a table to another? Your server may go out of service during the backup process.
0
 

Author Comment

by:dimensionav
ID: 40505920
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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40506002
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.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question