Maximum Rows and coloms in MY SQL Table

[letom]

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

[james438]

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.

[djr33]

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.

[letom]

@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 ?

[djr33]

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.

[traq]

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...?

[djr33]

(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.

[traq]

If WHERE or ORDER BY was indexed, then it would be fine:

-- 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):

-- can't index random orders
...  ORDER BY RAND()

-- can index `x`, but it won't help with LIKE
... WHERE x LIKE '%something%'

[djr33]

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).

[traq]

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.