Archiving SQL Data

[adw999]

Hi

I run an open source CMS and need a little help/advise. The system collects statistics into a database table, after a while this table gets quite large and with busy sites can get stupidly big. To reduce server load I introduced caching; this meant the statistics were read and stored into text files, and then only built once a week.

Although this is good to reduce server load it doesn't reduce the table size, so I started to code more caching. This version collects all data in the SQL table and puts the current results into text files. This data can then be used with new data that is stored into the database. Once a month all data is collated and put into the archive text files. So..

1. Check archive files exist, and weren't created more than a month ago

2. If the files do not exist create them and store the data into files

3. If they do exist, but are over a month old, fetch contents; run SQL to get previous months data and collate the two into the archive files

4. If they do exist, and ARE less than a month old; read the data and then collate data from DB table. Cache into weekly stats file.

The table schema is:

• id

• browser

• ip

• received

• refer

The code is:

• Attached, the text was too long.

I've checked it locally on 60k+ rows and I THINK it works, but I would like some additional thoughts. I can supply the stats SQL if anyone would like to test it for me.

Thanks
Ashley

PS. Live stats at 1024cms.com/index.php?p=stats

[superwormy]

I didn't look at the script, but just had some general notes...

If you're optimizing because a table of 60,000 rows is slow, then there's something else wrong (a lack of indexing or an unoptimized query most likely). In most cases, you really shouldn't even be considering archiving data unless you're talking about hundreds of millions of rows.

An alternative might be just to throw all of the data in other tables that never get queried... that way you don't need fancy scripts and all that.

[adw999]

The queries aren't slow, it's just the table size gets quite large and I was looking for ways to reduce it?

[superwormy]

Looking through the script, I don't see anything wrong with the script. I'm just saying... usually "The table is large" isn't a very good reason to start moving stuff into text files.

You're not really saving that much disk space as the file will take up lots of disk space too. And database performance really isn't going to be affected unless the table is genuinely large (hundreds of millions of rows) and even then, good indexing and query practices will probably keep your queries fast.

[adw999]

Ok thanks.

The size would be considerably reduced because I would only cache statistics results as serialised numbers. However I may leave it for now and look into better indexing

[superwormy]

Don't forget that serialize()'d data takes up a lot more space than just the numbers themselves... the serialize()'d string contains data types, array keys, etc. etc. etc. as well.