I'm about to start designing a new database structure in MySQL. It will involve a lot of data... this is consists almost immediately of hourly data for about 95 points, for every day, for roughly the past 6 years. I'm hoping to slim this down some, but that is a probable but worst-case scenario. Most of the data fields will be one to two digit integers, and a few will be decimals, probably all with at most 4 digits. Also, this databases will be read-focused, as the values will not be changed once entered, only analyzed, but unfortunately I don't believe I will have control over whether or not to use MyISAM.

I've been reading up on database warehouse design, but I haven't found a whole lot that seems to be a lot of use. Does anyone have any good experience dealing with large amounts of data in MySQL?

7/9/2007 1:04:53 PM

define "a lot"

[Edited on July 9, 2007 at 1:09 PM. Reason : b/c 95*24*365*6 is not a lot of data]

7/9/2007 1:08:04 PM

just always thought 4,993,200 rows of data would be a considerable amount at least.

what is a lot to you

7/9/2007 1:11:45 PM

that's about 200,000 data points. Don't worry too badly about it, that's not a huge amount to deal with

7/9/2007 1:13:26 PM

mysql can handle that pretty easily without considerations beyond the typical normalization and indexing techniques

7/9/2007 1:14:39 PM

for curiosity's sake, what would be a high number of tables?

[Edited on July 9, 2007 at 1:16 PM. Reason : hate sounding like an idiot, but i just haven't ever worked with very much data in MySQL]

7/9/2007 1:15:19 PM

tables or rows?

7/9/2007 1:15:42 PM

tables

7/9/2007 1:16:19 PM

pushing over 100 or so tables you will start seeing slowdowns in administrating the db. but as far as runtime efficiency goes, it shouldn't matter. Although if you push over several hundred tables, most likely you arent using the most efficient design possible, or you should start looking at splitting into multiple databases.

7/9/2007 1:18:56 PM

^ pretty much.

are you running your own server or is it hosted by dreamhost or something? chances are you won't have to worry about it until you start bearing a significant load on the hardware's limitations (disk space, memory or connections to the db)

[Edited on July 9, 2007 at 1:21 PM. Reason : basically, make sure your design is solid and you won't have problems ]

7/9/2007 1:20:39 PM

i'm not hosting it... this is actually for work. but, we don't have a designated SQL server yet. i'm tryin to get it designed and ready to roll when we do. chances are i'll be hooking up to a private host (ie dreamhost... though i'll probably go with lunarpages).

i'm definitely gonna spend more time on the design, was just curious what some boundaries were that i needed to look out for. thx for the replies fellas.

7/9/2007 4:47:51 PM