By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

One large table or many small tables?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.