How many rows in a database are TOO MANY?

Sql Problem Overview

I've a MySQL InnoDB table with 1,000,000 records. Is this too much? Or databases can handle this and more? I ask because I noticed that some queries (for example, getting the last row from a table) are slower (seconds) in the table with 1 millon rows than in one with 100.

Sql Solutions

Solution 1 - Sql

>I've a MySQL InnoDB table with 1000000 registers. Is this too much?

No, 1,000,000 rows (AKA records) is not too much for a database.

> I ask because I noticed that some queries (for example, getting the last register of a table) are slower (seconds) in the table with 1 million registers than in one with 100.

There's a lot to account for in that statement. The usual suspects are:

Poorly written query
Not using a primary key, assuming one even exists on the table
Poorly designed data model (table structure)
Lack of indexes

Solution 2 - Sql

I have a database with more than 97,000,000 records(30GB datafile), and having no problem .

Just remember to define and improve your table index.

So its obvious that 1,000,000 is not MANY ! (But if you don't index; yes, it is MANY )

Solution 3 - Sql

Use 'explain' to examine your query and see if there is anything wrong with the query plan.

Solution 4 - Sql

I think this is a common misconception - size is only one part of the equation when it comes to database scalability. There are other issues that are hard (or harder):

How large is the working set (i.e. how much data needs to be loaded in memory and actively worked on). If you just insert data and then do nothing with it, it's actually an easy problem to solve.
What level of concurrency is required? Is there just one user inserting/reading, or do we have many thousands of clients operating at once?
What levels of promise/durability and consistency of performance are required? Do we have to make sure that we can honor each commit. Is it okay if the average transaction is fast, or do we want to make sure that all transactions are reliably fast (six sigma quality control like - http://www.mysqlperformanceblog.com/2010/06/07/performance-optimization-and-six-sigma/).
Do you need to do any operational issues, such as ALTER the table schema? In InnoDB this is possible, but incredibly slow since it often has to create a temporary table in foreground (blocking all connections).

So I'm going to state the two limiting issues are going to be:

Your own skill at writing queries / having good indexes.
How much pain you can tolerate waiting on ALTER TABLE statements.

Solution 5 - Sql

If you mean 1 million rows, then it depends on how your indexing is done and the configuration of your hardware. A million rows is not a large amount for an enterprise database, or even a dev database on decent equipment.

if you mean 1 million columns (not sure thats even possible in MySQL) then yes, this seems a bit large and will probably cause problems.

Solution 6 - Sql

One million records is not a real big deal for a database these days. If you run into any issue, it's likely not the database system itself, but rather the hardware that you're running it on. You're not going to run into a problem with the DB before you run out of hardware to throw at it, most likely.

Now, obviously some queries are slower than others, but if two very similar queries run in vastly different times, you need to figure out what the database's execution plan is and optimize for it, i.e. use correct indexes, proper normalization, etc.

Incidentally, there is no such thing as a "last" record in a table, from a logical standpoint they have no inherent order.

Solution 7 - Sql

I've seen non-partitioned tables with several billion (indexed) records, that self-joined for analytical work. We eventually partitioned the thing but honestly we didn't see that much difference.

That said, that was in Oracle and I have not tested that volume of data in MySQL. Indexes are your friend :)

Solution 8 - Sql

Assuming you mean "records" by "registers" no, it's not too much, MySQL scales really well and can hold as many records as you have space for in your hard disk.

Obviously though search queries will be slower. There is really no way around that except making sure that the fields are properly indexed.

Solution 9 - Sql

The larger the table gets (as in more rows in it), the slower queries will typically run if there are no indexes. Once you add the right indexes your query performance should improve or at least not degrade as much as the table grows. However, if the query itself returns more rows as the table gets bigger, then you'll start to see degradation again.

While 1M rows are not that many, it also depends on how much memory you have on the DB server. If the table is too big to be cached in memory by the server, then queries will be slower.

Solution 10 - Sql

Using the query provided will be exceptionally slow because of using a sort merge method to sort the data.

I would recommend rethinking the design so you are using indexes to retrieve it or make sure it is already ordered in that manner so no sorting is needed.

Content Type	Original Author	Original Content on Stackoverflow
Question	Juanjo Conti	View Question on Stackoverflow
Solution 1 - Sql	OMG Ponies	View Answer on Stackoverflow
Solution 2 - Sql	amir beygi	View Answer on Stackoverflow
Solution 3 - Sql	Journeyman Programmer	View Answer on Stackoverflow
Solution 4 - Sql	Morgan Tocker	View Answer on Stackoverflow
Solution 5 - Sql	GrayWizardx	View Answer on Stackoverflow
Solution 6 - Sql	phoebus	View Answer on Stackoverflow
Solution 7 - Sql	Jé Queue	View Answer on Stackoverflow
Solution 8 - Sql	Thomas Bonini	View Answer on Stackoverflow
Solution 9 - Sql	jvilalta	View Answer on Stackoverflow
Solution 10 - Sql	Louis	View Answer on Stackoverflow