Optimizing MySQL Database structure for big big data

[eluser]Benjamin David[/eluser]
Hi,

I'm working on a project on which some database tables will be quickly subject to have millions of entries that will be often updated and even more often called. There will be a lot of JOIN between tables of millions entries, it's a real nightmare. The site will have around 700 000 page views a month.

I heard somewhere that instead of having, let's say a table like this :

Table_name : id_entry, name_entry, etc

Benchmark tests have revealed I could do somthing like creating a database for each entry and recreate the database structure.

This solution has been tested and works well but doesn't seem very scholar to me. Do you have interesting reading on this particular subject ? I've been Googling around but couldn't find anything serious or complete about this.

Thanks a lot

[eluser]Crafter[/eluser]
This si where the advise of a good DBA might come in very handy .

Did I hear you righjt when you said you will create a database for each entry. Sound like overkill to me.

Did you benchmark the possible overheads of connecting to all databases in a round robin manner compared to drawing a query from a single monolithic database?

You coulkd use contructs like view to reduce the complexity of the database structure.

In addition, I would look closely at the queries you are running and run an EXPLAIN command on them. Sometimes just placing an index in the right place, or even changing the order fo fields or where clauses might create huge performance gains.

[eluser]Sean Murphy[/eluser]
I agree with Crafter, creating a database for each entry doesn't sound like it would scale well even for a very small site. Not, a good idea.

Are you thinking of sharding, or table partitioning? These techniques are sometimes used to increase performance on incredibly large sites (YouTube, Digg, etc.), but I doubt you'd need to worry about it.

I think you'd have better success optimizing your queries and storage engines. Also, if you're constantly joining on the same tables, you might try de-normalizing your schema where you can.

Hope that helps!