Database level partitioning

Is it good to partition database by using multiple databases?

e.g. Blog System

Each user is split into different database by they user name (hashing
algorithm),

If I use two letter prefix, e.g. aa...zz, I will have total (26x26 =
676 db)

So assuming originally I have 10M articles in a single DB, now I only
have 1476 articles per db, which is very scalable in nature.

Any comments?

>Is it good to partition database by using multiple databases?

It depends on how the data is used.

>e.g. Blog System
>
>Each user is split into different database by they user name (hashing
>algorithm),
>
>If I use two letter prefix, e.g. aa...zz, I will have total (26x26 =
>676 db)

Does it take 676 queries to find out the next article in a thread?
If so, you've made a TERRIBLE choice. On the other hand, if 'user name'
means the OWNER OF THE BLOG, not the poster of a comment, it might not
be such a bad idea, segregating posts of each blog into a separate database.


>So assuming originally I have 10M articles in a single DB, now I only

MySQL performs rather well with lots more records in a single TABLE.

>have 1476 articles per db, which is very scalable in nature.

*NOT* if there are many queries which do not know the username ahead
of time, and therefore involve queries to all the databases. If
there are many of these queries, and they are run often, your
proposed splitting is not a good idea.

>Any comments?

Don't be too quick to assume that partitioning the database as you
propose is not a pessimization. Also, you may incur more
permission-checking if you split by database rather than by table
in the same database.

For example, consider a sales order tracking system. It's a bad
idea to partition the database by product, because many orders will
order more than one kind of product (so printing an invoice will
require a query per product). It's a bad idea to partition the
database by salesman, since you will want to generate reports of
the total number of a particular item sold for the whole company.
It might not be such a terrible idea to partition the database by
fiscal year, but you still have to deal with ordering in one fiscal
year and shipping and payment in another.

On 18 Jan, 03:38, howa <howac...@gmail.com> wrote:
> Is it good to partition database by using multiple databases?
>
> e.g. Blog System
>
> Each user is split into different database by they user name (hashing
> algorithm),
>
> If I use two letter prefix, e.g. aa...zz, I will have total (26x26 =
> 676 db)
>
> So assuming originally I have 10M articles in a single DB, now I only
> have 1476 articles per db, which is very scalable in nature.
>
> Any comments?

I think the simple answer is no

On Thu, 17 Jan 2008 19:38:38 -0800 (PST), howa
<howachen@gmail.com> wrote:

>Is it good to partition database by using multiple databases?
>
>e.g. Blog System
>
>Each user is split into different database by they user name (hashing
>algorithm),
>
>If I use two letter prefix, e.g. aa...zz, I will have total (26x26 =
>676 db)
>
>So assuming originally I have 10M articles in a single DB, now I only
>have 1476 articles per db, which is very scalable in nature.
>
>Any comments?

You may want to read:
http://dev.mysql.com/tech-resources/...titioning.html

and view:
http://www.mysql.com/news-and-events...g-20061205.php
--
( Kees
)
c[_] He who regards all things as one is a companion of Nature. (#265)