Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Maximum Rows and coloms in MY SQL Table

  1. #1
    Join Date
    Jul 2012
    Posts
    197
    Thanks
    55
    Thanked 3 Times in 3 Posts

    Default Maximum Rows and coloms in MY SQL Table

    Hi,

    What is the maximum number of Rows we can store in a table of MYSQL.?

    For columns, I mean is it better to store upto 80, even if the limit is more than that off. Is it true ?

    If iam storing Hexa - Trillions(unlimited) rows in a table .. How can i retrieve the results faster ? How can i do pagination in My SQL ?

    Your answers and discussions are highly appreciated

    Regards
    Tom

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,734
    Thanks
    82
    Thanked 90 Times in 88 Posts

    Default

    From what I have read on the MySQL website there is no limit to the number of rows. It is based on the size allowed for your database. For example on GoDaddy I can have many different databases, but they are limited to 1GB each.

    Scalability and Limits:
    Support for large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 200,000 tables and about 5,000,000,000 rows. ref
    E.7.4. Table Column-Count and Row-Size Limits
    There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors. ref
    I would store whatever number of columns is easiest to keep track of.

    I am not sure about trillions of rows. I can tell you that MySQL is very good at retrieving data very quickly compared to other web design languages.
    Last edited by james438; 02-26-2013 at 04:28 PM.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. The Following 2 Users Say Thank You to james438 For This Useful Post:

    bernie1227 (02-28-2013),letom (02-27-2013)

  4. #3
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    MySQL is very efficient, but the numbers you mentioned are extreme. I think it will be possible to find a way to make all of that work, but it might not be so fast then. If you're going to have that much content, then you may want to look into some kind of custom system involving caching or something-- just imagine what Google must do with all of the website data it searches.
    I can't imagine the need for that much information without a huge project/company behind it. I'd suggest that if you start to actually reach the limits of technology like MySQL that you may want to hire a consultant to specifically deals with these things. I don't think there's any simple alternative (like different database software) that would be much better than MySQL.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  5. The Following 2 Users Say Thank You to djr33 For This Useful Post:

    bernie1227 (02-28-2013),letom (02-27-2013)

  6. #4
    Join Date
    Jul 2012
    Posts
    197
    Thanks
    55
    Thanked 3 Times in 3 Posts

    Default

    @James @Daniel

    Thanks for the messages from both of you.
    From the statements of both of you i understood, the scalability is not a problem, it depends on the hardware and HDD Capacity. But the problem arise is in the case of retrieval of data , Huge amount of records reduces the speed of retrieval , to do retrieval faster we can do pagination or clustering.
    any tutorials available for pagination and clustering ?

  7. #5
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    to do retrieval faster we can do pagination or clustering.
    Not exactly. Certainly displaying all results would take a very long time. But that's not what I was talking about. Displaying a single result is very fast in MySQL, but it does need to search through many results. (Of course, it depends on your search algorithm; if you use "sort by" or "where", it will need to look at all rows; if you just look for the first row [based on the existing index] then that would be faster].) With a normal database, that won't take too long for one result. But if you literally have millions or billions of rows, that single operation will become slow.
    So pagination is necessary even for a small database (it would be odd to display 1000 rows on an HTML page anyway), but it won't solve the problems for a huge database.


    As for pagination, you can find many tutorials by googling "mysql pagination". Personally I like the one on http://www.php-mysql-tutorial.com/ (the site isn't well organized, but the tutorials are good). There are lots out there though, all with similar methods I think.
    I'm not sure about clustering. I haven't heard of that for MySQL, but maybe it would help here.

    Another option is caching-- storing the results from common queries. But that only works if you have lots of computations or a subset of results that will be cached-- if you frequently use every result, then you'd have a table equal in size to the original. So the use of caching must be strategic and varies by project.
    Last edited by djr33; 02-27-2013 at 06:20 PM.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  8. The Following 2 Users Say Thank You to djr33 For This Useful Post:

    bernie1227 (02-28-2013),letom (03-10-2013)

  9. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    With a DB of that size, you need to make efficient use of indexing and be careful what kinds of queries you run. You need to avoid full table scans at all costs - for example, running a query like SELECT something FROM column WHERE column LIKE '%this%'; on a trillion rows would be idiotic.

    This is exactly what indexes are for - if you can look up rows in an index, you don't have to look through each table to find the rows you need. There's a book by Bill Karwin called SQL Antipatterns that I would wholeheartedly recommend. You might also start reading up at Rick James' website.

    It might also be necessary to partition your DB across several machines to keep it efficient. What you need to do will depend on your specific application, however.


    Daniel, your link is broken...?

  10. The Following User Says Thank You to traq For This Useful Post:

    letom (03-10-2013)

  11. #7
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    (Fixed the link. I guess it needed the www.)

    This is exactly what indexes are for - if you can look up rows in an index, you don't have to look through each table to find the rows you need. There's a book by Bill Karwin called SQL Antipatterns that I would wholeheartedly recommend. You might also start reading up at Rick James' website.
    So it's fast as long as you use the existing indices in the order that they exist, right?
    But it would be slow if you did anything else like ORDER BY or WHERE, right? It would be extra slow for WHERE x LIKE '%something%', but even just WHERE x=1 or ORDER BY X, it would need to look at all rows I think.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  12. #8
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    If WHERE or ORDER BY was indexed, then it would be fine:
    Code:
    -- assuming `x` is indexed
     ... WHERE x < 5
    
    ... ORDER BY x
    However, if it's not indexed (or cannot benefit from an index), then it will force a full table scan (and on a trillion rows, will probably time out - maybe even lock things up):
    Code:
    -- can't index random orders
    ...  ORDER BY RAND()
    
    -- can index `x`, but it won't help with LIKE
    ... WHERE x LIKE '%something%'

  13. The Following 2 Users Say Thank You to traq For This Useful Post:

    bernie1227 (02-28-2013),djr33 (02-27-2013)

  14. #9
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    Right, ok. So if you had a database of movies and wanted to search by title, that would be problematic, unless that title was already the index (or corresponded to a numerical index).
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  15. #10
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    You can have multiple indexes on a table. If you have frequent queries, you should create indexes for them if possible.

    As far as your example goes, a numeric index wouldn't help with searching by title. You'd need to index the titles.

  16. The Following User Says Thank You to traq For This Useful Post:

    letom (03-10-2013)

Similar Threads

  1. merge rows within the same table
    By regicidedelferoz in forum PHP
    Replies: 2
    Last Post: 07-06-2011, 01:53 PM
  2. show table rows (new to js)
    By JohnShell in forum JavaScript
    Replies: 1
    Last Post: 09-09-2010, 06:44 AM
  3. setting maximum width for table cell
    By gib65 in forum CSS
    Replies: 3
    Last Post: 04-22-2010, 01:18 AM
  4. update rows in table
    By gurmeet in forum PHP
    Replies: 1
    Last Post: 03-11-2009, 05:19 AM
  5. How to set table cols and rows
    By sinox in forum MySQL and other databases
    Replies: 2
    Last Post: 05-24-2007, 05:14 AM

Bookmarks

Posting Permissions

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