469,898 Members | 1,620 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

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 6341
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

36 posts views Thread by Andrea Griffini | last post: by
55 posts views Thread by Jonas Smithson | last post: by
1 post views Thread by Good Man | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.