One large table or many small tables?

[es22]

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.

[Aggro]

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

[Aggro]

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

[Aggro]

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