[RESOLVED] Huge table VS Many Tables

perrohunter

Hello everyone

Im planning do start a project which may cause my tables to turn insanely huge (billions of rows) , so it comed into my mind the question:

Will splitting and sorting the rows by month will improve the performance of my app ??

This is because I think having a single table is more comfortable , so far, multiple table sounds like less weight for the server, even tho i dont have a single idea on how to execute a query to select from multiple tables

Any ideas or comments ? thanks😃

MarkR

perrohunter wrote:
Will splitting and sorting the rows by month will improve the performance of my app ??

Maybe.

You can't really tell at the moment. It really depends what kind of queries you're doing.

In most databases, it won't significantly affect the size of the data to put it in several tables vs one table. However, it might affect the speed of queries.

It depends what kind of queries you have, and what indexes you have.

If you find yourself doing UNIONs all over the place, it might be detrimental to both the runtime performance, and the code maintenance of the application.

Therefore my advice would be to keep it in one big table until your profiling data* tell you that you need to split it up.

Mark

You do HAVE profiling data, right?

NogDog

How long does data need to be retained? Perhaps a cron script or maybe a database trigger could be used to periodically delete records older than X number of days, months, years, or whatever?

perrohunter

unfortanely the data needs to be hold permanently, today i made my first test, in average its 500,000 rows per month on 32 fields per table, about 36 million rows since 2000 year O_O , and continiue to grow.

On other forums they said multiple tables would be better cuz if i the query dont need em, wont touch them =P , and i heard that joins can do the job =P

But joining things sounds so difficult =(

what do you thnk ?

NogDog

I'm not real comfortable with separate tables per month (or whatever other arbitrary time-based division you might choose) as you will likely need to deal with data across that time boundary (I suppose the "joins" you referenced?). That just seems messy to me. But on the other hand I see where it might provide better performance in certain situations.

This would probably drive database normalization gurus crazy, but if the vast majority of your queries only need data up to some fixed number of days/weeks/months ago, you could have two tables, let's call them "recent_data" and "all_data". Whenever you add a record, you would add it to both tables. However, the "recent_data" table would periodically be reduced by a query to delete all rows with a timestamp older than X number of days/weeks/months ago. Then those most common queries would just deal with the recent data, while you could still query the "all_data" table for those few queries that need it (and assuming such queries are not as time-critical as the others).

Just a thought. Being as I am by no means a database expert, I'll not be insulted if any of you gurus shoot it down (in a reasonably civil manner 😉 ).

tomhath

If you're looking at 500000 rows/month, it'll take over 150 years to get a billion rows. So maybe it's not that big after all.

A lot depends on what happens to the data after it's inserted into the table(s). Occasional reports? Or frequent ad hoc queries? Given the number of rows you're looking at, I'd go with one big table and a couple of indexes for querying it.

bpat1434

If you're holding data permanently and stuff from say 2005 isn't being updated, it may be better for you to start an archival project. All data prior to 2005 will have it's own HTML page (or set of pages) with all the data from the database. This would reduce the load on your server, and could potentially make archived items appear faster 😉

I personally am a fan of splitting tables up. You know, have a users table with userIDs and reference the userIDs in other tables. Could make things faster, but you'd have to run some tests to figure that out.

perrohunter

Ive been making alot of speed test and found out that a huge huge table is faster

These are my results:

I made the test with only 5 months (Jan,Feb,May,Apr,Mar) , and using only one category , with a total of 1,306,129 rows

Multiple tables with about 280,000 rows each: with simple query selecting WHERE field='value' that returns 2040 results takes: 71.0699 Seconds with simple displaying in screen script

Singles table with 1,306,129 rows : with simple query selecting WHERE field='value' that returns 2040 results takes: 38.2567 Seconds with simple displaying in screen script

So, I think the idea of mixing everything on a single table worked just faster =)

Thanks everyone !

bpat1434

Did you optimize your tables? Do you have indexes on certain columns with foreign keys and primary keys set up properly?

Not setting up your test environment right can skew your results. I'm not saying they're wrong, just trying to make sure you get as much "proper" information as possible.

perrohunter

I have indexes, optimized tables, and all the stuff the book said =P