why do we divide a mysql table into many smaller tables?

Question

it seems that it is a common practice to divide the data of one table into many databases, many tables to improve performance, i can understand the many databases part, because more databases provides more CPUS, more memories, more IO capacity. but many tables? why not just use mysql partitions http://dev.mysql.com/doc/refman/5.1/en/partitioning.html?

update: i dont mean normalization. i mean divide a table of N records into e.g. 10 tables each of the small table have N/10 records

update2: thanks @Johan for the clarification of sharding and partition, especially point out the hot property of the data.

The small question @Johan did not answer is: for a simple example, lets say we have a user table, it has a userid column(bigint). I think it is easier to use mysql-partition to divide the table into partitions based on userid automatically, there seems no benifit to divide the table into small tables manually(based on userid), am i right?

Answer 1

I think you have a few terms mixed up here.

All your data goes into one database (aka schema). In a database you can have tables.

e.g.

table employee
   id integer
   name varchar
   address varchar
   country varchar

table office
   id integer
   employee_id integer
   address varchar

Inside tables you have fields (id, name, address) aka columns. And tables have one or more rows.
An example for table employee:

id  name        address           country
----------------------------------------------------
1   John        1 Regent Street   UK
2   James       24 Jump Street    China
3   Darth Vader 1 Death Star      Bestine, Tatooine

So much for the basics.

Why partitioning
Now suppose that we have lots and lots of people (rows) in our database.
Remember this a galactic database, so we have 100 billion records.
If we want to search trough this fast it's nice if we can do this in parallel.
So we partition the table (say by country) and then we can have x servers looking in 1 country each.
Partitioning across servers is called sharding.

Or we can partition e.g. historical data by year, so we don't have to go through all the data just to get the recent news. We only have to go through the partition for this year. This is called partitioning.

What's the big difference between sharding can just partitioning?

Sharding
In sharding you anticipate that all your data is relevant, and equally likely to be queried. (e.g. google can expect all their data to be queried; archiving part of their data is useless for them).
In this case you want lots of machines to look though your data in parallel, where each machine does part of the work.
So you give each machine a different partition (shard) of the data and give all the machines the same query. When the results come out you UNION them all together and output the result.

Basic partitioning
In basic partitioning part of your data is hot and part is not. A typical case is historical data, the new data is hot, the old data hardly gets touched.
For this use case it is pointless to put the old data in separate servers. Those machines will just wait and wait and do nothing because nobody cares about the old data except some auditors who look at it once a year.
So you partition that data by year and the server will automatically archive the old partitions so your queries will only look at one (maybe 2) years of data and be much faster.

Do I need partitioning?
You only do partitioning when you have lots and lots of data, because it complicates your setup.
Unless you have more than a million records you don't have to consider partitioning.^*)
If you have more than a 100 million records, you should definitely consider it.^*)

For more info see: http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
and: http://blog.mayflower.de/archives/353-Is-MySQL-partitioning-useful-for-very-big-real-life-problems.html
See also wiki: http://en.wikipedia.org/wiki/Partition_%28database%29

---

^*) These are just my personal heuristics YMMV.