473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

One large table or many small tables?

Hi,

I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.
Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

How many rows can mysql handle in one table?

Thanks,

Ehud.
Jul 20 '05 #1
3 6521
es22 wrote:
How many rows can mysql handle in one table?


You will propably run out of free disk space before encountering this
limit. I'm not even sure if there is any limit how many rows you can
have. Anyway, the maximum table size is somewhat the same as what is the
largest file your os can handle. (2GB-4GB perhaps?)

Basicly you can find out how many only by testing. Create a script that
adds dump data into MySQL which has the same data types (and sizes)
which you are planning to use.

Actually, against my previous suggestion, you might need to do it with
many-tables-style, if the table size on disk grows too big for the os to
handle. Another solution would be to delete old data, or transfer it to
another file(s).

My guess would be that it can hold at least about 10-20 million rows (If
os limits the file size to 2GB). But this is just a guess, the real
value can be very different.

http://dev.mysql.com/doc/mysql/en/Table_size.html
Jul 20 '05 #2
es22 wrote:
How many rows can mysql handle in one table?


You will propably run out of free disk space before encountering this
limit. I'm not even sure if there is any limit how many rows you can
have. Anyway, the maximum table size is somewhat the same as what is the
largest file your os can handle. (2GB-4GB perhaps?)

Basicly you can find out how many only by testing. Create a script that
adds dump data into MySQL which has the same data types (and sizes)
which you are planning to use.

Actually, against my previous suggestion, you might need to do it with
many-tables-style, if the table size on disk grows too big for the os to
handle. Another solution would be to delete old data, or transfer it to
another file(s).

My guess would be that it can hold at least about 10-20 million rows (If
os limits the file size to 2GB). But this is just a guess, the real
value can be very different.

http://dev.mysql.com/doc/mysql/en/Table_size.html
Jul 20 '05 #3
es22 wrote:
How many rows can mysql handle in one table?


You will propably run out of free disk space before encountering this
limit. I'm not even sure if there is any limit how many rows you can
have. Anyway, the maximum table size is somewhat the same as what is the
largest file your os can handle. (2GB-4GB perhaps?)

Basicly you can find out how many only by testing. Create a script that
adds dump data into MySQL which has the same data types (and sizes)
which you are planning to use.

Actually, against my previous suggestion, you might need to do it with
many-tables-style, if the table size on disk grows too big for the os to
handle. Another solution would be to delete old data, or transfer it to
another file(s).

My guess would be that it can hold at least about 10-20 million rows (If
os limits the file size to 2GB). But this is just a guess, the real
value can be very different.

http://dev.mysql.com/doc/mysql/en/Table_size.html
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

36
by: Andrea Griffini | last post by:
I did it. I proposed python as the main language for our next CAD/CAM software because I think that it has all the potential needed for it. I'm not sure yet if the decision will get through, but...
4
by: jeff brubaker | last post by:
Hello, Currently we have a database, and it is our desire for it to be able to store millions of records. The data in the table can be divided up by client, and it stores nothing but about 7...
55
by: Jonas Smithson | last post by:
I've seen a few attractive multi-column sites whose geometry is based on pure CSS-P, but they're what you might call "code afficionado" sites, where the subject matter of the site is "coding...
5
by: charlies224 | last post by:
Hi, I am using SQL 2000 and has a table that contains more than 2 million rows of data (and growing). Right now, I have encountered 2 problems: 1) Sometimes, when I try to query against this...
1
by: Good Man | last post by:
Hi there I'm developing a large web application. Part of this web application will be storing numerical chart data in a MySQL table - these numbers will be already calculated, and are just being...
24
by: Salad | last post by:
Every now and then I see ads that state something like "Experience with Large Databases ...multi-gig...blah-de-blah" And I have to laugh. What's the difference between a large or small database? ...
2
by: ARC | last post by:
Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and...
10
by: nflacco | last post by:
I'm tinkering around with a data collection system, and have come up with a very hackish way to store my data- for reference, I'm anticipating collecting at least 100 million different dataId...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.