/    Sign up×
Community /Pin to ProfileBookmark

PHP/MySQL data archiving

Copy linkTweet thisAlerts:
Sep 25.2007

I need a little help with a system I am setting up. This is one of the largest systems I have setup and I want some advice on the data model.

In my MySQL database I have 4 tables that I will be using. There will be approximately 150 records added to two of the tables each day, and approximately 1500 records added to the other two tables. So as you can see; these tables are going to grow fast.

The 150 records/day tables – approximately 50 different fields

The 1500 records/day tables – approximately 140 different fields

What I am concerned about is performance when people are trying to access the data. 90% of the queries will be for data that is 1 month old and newer, but they want the option to search data that is as old as 2-3 years old.

My plan is to run a cron job every night and any records older than 1 month will be moved into this separate ‘archive’ database. This ‘archive’ database will mirror the ‘live’ database as far as table fields. What I’m not sure is if I should create another database for the archive data. Or should I just create an archive table for each live table. Which of these would be the best approach? Any feedback would be greatly appreciated.

to post a comment
PHP

1 Comments(s) ↴

Copy linkTweet thisAlerts:
@Mester_PedizSep 25.2007 — Well i'm not the most hardcore MySQL dude out there, but i would think that MySQL is good enough when the users search stuff, as long as the search criteria and queries is kept simple.... What i'we usually heard and with my use of MySQL ?

But of cause now when i'm not that much into how all the DB stuff works, only how to use it, it can't say if theres is any problems involved when getting up to larger sizes of data.
×

Success!

Help @jesseainskeep spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.17,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @nearjob,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,
)...