Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Location
    chennai , tamil nadu , India
    Posts
    17

    Unanswered: Max number of tables in Mysql database

    1 . Is there a maximum number of tables LIMIT per database in Mysql ?
    2. splitting into smaller tables will increase performance compared to having large tables isnt it ?

    chris

  2. #2
    Join Date
    May 2002
    Posts
    16
    1 . Is there a maximum number of tables LIMIT per database in Mysql ?
    2. splitting into smaller tables will increase performance compared to having large tables isnt it ?
    1. could'nt remember a database limit in mysql.

    2. yes. build joins instead of large tables.

  3. #3
    Join Date
    May 2002
    Location
    chennai , tamil nadu , India
    Posts
    17

    custom php perl mysql programming

    Thanks for the reply

    Yes mysql join makes mysql queries efficient

    But still I want to know how many tables a mysql database can hold ?
    Chris, Developer, Chrisranjana.com,
    Ruby on Rails Developers,
    Php laravel Programmers,
    http://www.chrisranjana.com

  4. #4
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200

    Re: custom php perl mysql programming

    Originally posted by chrisranjana
    But still I want to know how many tables a mysql database can hold ?
    When using MyISAM table, the limit will be determined by your operating system, ie. the number of files you can have in one directory.

    Using InnoDB tables removes that limitation by allowing multiple tables within a single datafile (tablespace).

    The maximum tablespace size is 4 billion database pages. By default, a 'page' is 16K, but it is possible to recompile with 64K pages. This will allow a single tablespace size of ... 'BIG'.

    A table will obviously take up at least one database page, meaning you can theoretically have a maximum of 4 billion tables per tablespace.

    The number of tablespaces will again be limited by your operating system. Using NTFS on NT, the limit is 4,294,967,295. I imagine UNIX will be limited by the number of inodes. However, before reaching the physical limit of files you wil probably run into an issue with the maximum number of 'open' files allowed.

    The first limit you hit will undoubtedly be that of your wallet while buying disk drives.

  5. #5
    Join Date
    May 2002
    Location
    chennai , tamil nadu , India
    Posts
    17

    custom php perl freelance mysql programming

    just one word "WOW !"
    Chris, Developer, Chrisranjana.com,
    Ruby on Rails Developers,
    Php laravel Programmers,
    http://www.chrisranjana.com

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by infonama View Post
    doesnt more join make you DB slow in retrieving data?
    in a word: no

    compare to the alternatives:

    1. you need the data but don't perform the join, leaving your query results useless

    2. you decide not to normalize, resulting in even slower queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by infonama View Post
    got your point already in my posted thread. thanks for all the assistance
    well, you asked twice, i answered twice

    what was i supposed to do, leave the question unanswered? what if somebody else stumbles over this thread like you did?

    by the way, you've won this month's prize for dredging up the oldest dormant thread -- eight years!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •