On Apr 9, 2005, at 8:05 AM, olli wrote:
hi,
if your table is indexed, i think it can theoretically hold 4.294.967.295 rows, because that's the maximum for an unsigned integer value in mysql and indexing doesn't work with bigint types as far as i know. but, i'm not really sure about that.
I would suggest checking the manual, where answers are readily available, rather than speculating.
MySQL limits tables to 8 million terabytes, so table size is currently limited by your OS/filesystem, in practice. Since most tables aren't that big, the default structure for a MyISAM table limits you to 4Gb in size. You can change this at table creation (preferred), or later with an ALTER statement, using the AVG_ROW_LENGTH and MAX_ROWS options. You can check the maximum size of an already defined table with SHOW TABLE STATUS.
References: <http://dev.mysql.com/doc/mysql/en/table-size.html> <http://dev.mysql.com/doc/mysql/en/full-table.html> <http://dev.mysql.com/doc/mysql/en/create-table.html> <http://dev.mysql.com/doc/mysql/en/show-table-status.html>
Am 09.04.2005 um 11:42 schrieb Daniel Kiss:
Hi All,
I would like to know how big is the biggest database that can be handled effectively by MySQL/InnoDB.
Like physical size, number of tables, number of rows per table, average row length, number of indexes per table, etc.
Practically, what if I have a master/detail table-pair, where the master contains about 30 million rows, the detail in average contains 50 row for each master row, so about 1.5 billion rows in total?
I know that the performace heavily relies on the hardware, but let's assume that the MySQL server runs on a high-end machine with about 2GB of RAM.
Performance is largely determined by db design, indexing, and how your queries are written. For example, a full table scan of your detail table would look at 50 times more rows than an index lookup of just the rows which match the one master row in question. Quadrupling your disk speed wouldn't help much relative to adding the index. In other words, all else being equal, faster hardware gives faster performance, but optimizing your table structure, indexes, and queries has a bigger impact on performance. Tuning your server parameters to match your usage and resources can also make a big difference.
If you haven't yet chosen your "high-end machine with about 2GB of RAM", you should know that "Which hardware is best?" is a FAQ on this list, so you should be able to find numerous suggestions by searching the list archives <http://lists.mysql.com/mysql>.
Does anyone have experience with extremely large databases?
You may find Jeremy Zawodny's book, "High Performance MySQL", useful. <http://www.oreilly.com/catalog/hpmysql/>
Thank you, Daniel
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
