Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table.

So, what are the best options to store all this data in our database and afterward access to this data (search in the database) ?

No need to say that it must be fast.


share|improve this question

migrated from Jun 24 '12 at 16:05

This question came from our site for system and network administrators.

Perhaps add an example row and query? – Andomar Jun 24 '12 at 16:09

100 million rows in a table does not make a database "huge" by today's standards

This is also not MySql related in most ways - because you leave pretty fat the server software level and talk hardware.

The standard answer to this is that it all depends on your workload profile. Is it transactional, reporting, OLAP?

You mentioned that your largest table is 100 million rows. How does this compare to the other tables? Are they much smaller, or about the same size?

You may want:

  • As much RAM as you can. It is nice that servers these days should be able to load this size table in memory, isn't it?

  • As much disk performance as you can get.

This is where special hardware gets into the game fast. The last project I did in this area had 3x768gb flash disks with write back cache and 2x96gb with 48 cores each. However, this was an Oracle RAC and probably way beyond what MySQL could handle.

Anyhow, your best bet would be to go SSD for the IO side, memory for the buffer side, and with something in the 100 million row range you should be able to keep a significant part of that in memory. Using a 64gb server and 48gb buffer for that table I get 480 bytes per row (table, index). That wont be exact, but it gives you an idea that you can easily (64gb servers are quite cheap) fit the data in memory.

Here is a hint: Databases are not large when the fit in memory. They are large when you can not fit them into decent server hardware.

share|improve this answer
Ok. My bad for the huge. Database isn't really my field. It's so far the biggest database i had to deal with. The others tables are pretty small. Only couple hundreds row and almost nothing inside. There is only this big table. Who is going to grow daily as well. Probably 20 000 row a day. My knowledge is actually pretty bad about DB, so sadly i don't understand this line: "Sadly, it all depends. Is it transactional or reporting / data warehouse?" – bl0b Jun 24 '12 at 17:04
Transactional / tons of updates id different than analytical (tons of groupings for reporting). One is normally disc limited, the other normally more ram / Processor limited. – TomTom Jun 24 '12 at 17:12

Since the speed of any query against this database will depend very much on what search values you put into the query, one option could be creating a simple setup with a mysql database, then generating some testdata which are similar to the real data, creating a few typical queries and also the heaviest queries you can imagine and just try it out. you need this anyway of you need to decide between different database systems down the road.

then follow up any suggestions in from mysql tools about indexing etc.

i guess in your situation i would actually wanted proof that a simple mysql database did NOT solve the problem before starting to look at non free options.

there are also other free options, like Postgres.

both of these would normally have no problems with 20000 rows added every day.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.