106

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.

alex
  • 479,566
  • 201
  • 878
  • 984
Juanjo Conti
  • 28,823
  • 42
  • 111
  • 133

10 Answers10

122

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:

  1. Poorly written query
  2. Not using a primary key, assuming one even exists on the table
  3. Poorly designed data model (table structure)
  4. Lack of indexes
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 4
    5. Outdated server specs – Sneakyness Dec 18 '09 at 04:21
  • 21
    @Brimstedt: I also always thought the noun should be "Indices", but I don't think I ever saw anyone using it for databases: from Wikipedia: http://en.wikipedia.org/w/index.php?title=Index_(database)&oldid=332319104 to Mr. Coding Horror: http://www.codinghorror.com/blog/archives/000638.html. There is this interesting SO post on the topic: http://stackoverflow.com/questions/1001366/. – Daniel Vassallo Dec 18 '09 at 11:12
  • 7
    6. not enough memory allocated for innodb's various caches – Jason Jan 07 '10 at 13:50
  • for better performance whether I must use PrimaryKey? What about using other keys such as Index, Unique? May I use these? thanks – user1844933 Jul 02 '14 at 09:38
  • Maybe computer is hogged up with memory as Jason said and cuts off in the middle of the process – rassa45 Jun 27 '15 at 05:20
  • @user1844933 - In some situations, the PK is more efficient. But do not take the rule as absolute. – Rick James Apr 19 '19 at 05:54
75

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 )

BBdev
  • 4,898
  • 2
  • 31
  • 45
amir beygi
  • 1,234
  • 8
  • 13
  • 11
    Would adding a "primary key" to a column (by selecting the auto increment) be indexing? – Nathan May 20 '12 at 01:42
  • 8
    @Nathan , actually when you assign a column to be a primary key, it automatically becomes indexed, but every table can have only one primary key, if you need to add index for some column, to optimize the queries use this http://stackoverflow.com/a/3002635/932473 – dav Jun 22 '13 at 11:39
  • I have table with one trilions but selecting IN LIFO format data is slow ? – Saurabh Chandra Patel Mar 24 '16 at 03:37
  • Define not having problems. How long does the most complex query take? We have a table with 100 million rows and a client expects queries to be done in 5 seconds max, regardless of what grouping or ordering criteria they use. Our indexes could be improved but before we lock everything trying to add an index – Joe Yahchouchi Jun 21 '18 at 06:09
  • 20% of production tables (according to an old study) have more than 1M rows. I have seen a few with several _billion_ rows. – Rick James Apr 19 '19 at 05:56
  • A basic question for you, how many results from a query is too many? Let's say I do a query that returns 2000 results. Does the amount of results directly reflect the performance/amount of time the query takes? Sorry if noob question. – jmchauv Jan 16 '20 at 16:36
20

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

Journeyman Programmer
  • 1,346
  • 1
  • 9
  • 15
  • 7
    While this is a good idea, this answer itself isn't good to give to a newbie. The output from EXPLAIN is not very intuitive... – nickf Jan 03 '10 at 14:16
  • 20
    There's no other tool to help you examine the queries, so better start learning `EXPLAIN` - newbies or not. – nos Jul 25 '10 at 01:17
  • 39
    would be nice if someone can **EXPLAIN** `EXPLAIN` ;) – Jo E. May 20 '14 at 17:20
  • 8
    @Deadpool [Mysql Explain Explained](http://www.slideshare.net/phpcodemonkey/mysql-explain-explained) – Sithsu Jul 11 '15 at 10:28
19

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.
Morgan Tocker
  • 3,370
  • 25
  • 36
  • 2
    Edit: Advice about ALTER TABLE creating temporary tables is a little dated. MySQL 5.5 has a fast index create, and 5.6 now has online DDL. – Morgan Tocker Sep 25 '14 at 14:56
3

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.

GrayWizardx
  • 19,561
  • 2
  • 30
  • 43
3

Register? Do you mean record?

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.

phoebus
  • 14,673
  • 2
  • 33
  • 35
3

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

Jé Queue
  • 10,359
  • 13
  • 53
  • 61
2

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.

Andreas Bonini
  • 44,018
  • 30
  • 122
  • 156
  • 2
    Technically, the size of the table could also be limited by the max file size of the file system you are using. – tster Dec 18 '09 at 04:48
0

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.

jvilalta
  • 6,679
  • 1
  • 28
  • 36
0

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.

Louis
  • 4,172
  • 4
  • 45
  • 62