Mysql – More table or more database for better performance

MySQLperformance

Lets say 1 website need 10 database table to work and i want a database pool that 40.000+ website will be inside.

Which option is better for server performance?

1) 400 databases each have 1000 table?

2) 4000 databases each have 100 table?

Update: I see i need to put some more details, sorry for trying to ask simple as much as i can.
Site will based on wordpress multisite with http://wordpress.org/extend/plugins/hyperdb/

1 website with 10 database table generally wont pass 6-7 mb as total database info. There will be 9-10 heavy website that will have serious database info, i will put them in a special database. ( lets say 4096 database, 96 reserve for vip,heavy sites and split all other sites in 4000 database )

Server is:
http://www.wiredtree.com/managedhybrid/
After reaching some point, i am planning move to pure dedicated server which will based on raid10 15k RPM SAS mysql discs and different media discs

So 40.000 sites but all of them sharing same php files, just different databases and different upload folders. This is how wordpress.com working. Same rules but my sites wont be a blog.

Please ask what other info do you want, i will try to explain more. I am very sorry if i break any rules or something.

( Sorry for bad english too 😛 )

Best Answer

Performance shouldn't be a concern: security, consistency, data integrity should be

Security: If each web site has a database this implies that the data for that web site is private and separate. So 40000 databases with 10 tables each.
Consistency: managing 40k websites requires utter consistency. Why pick 400x1000 or 4000x100? How will you separate tables? How do you know which site uses which set of tables in which DB?
Data integrity: If you restore one database, do you really want to reset the data for another 399 or 3999 websites too?

So: 40k databases, one per web site.

Finally, do you have 40k web sites or is this premature optimisation?

Related Solutions

MySQL database design

I'll take a crack at this. Note, that I've just listed fields that are necessary. If you have additional data you want to store for in those tables, feel free to add them (so long as they are normalized).

While you did state that you would use a different database for each region, I designed this so that they could all work in one database. Right now you may have only 5 regions, but this offers the flexibility to easily add more:

table: region
regionID        varchar(2)
regionName      varchar(50)

Defines data for each realm. I went with a surrogate key (realmID) because it's possible that realmName might not be unique. This assumes that each realm can only belong to one region (regionID needs to be a foreign key to regionID on the region table):

table: realm
realmID         int
regionID        varchar(2)
realmName       varchar(50)

Defines data for each user (seller/buyer). I decided that it would be better if it had a surrogate key (sellerID) because seller/buyers first/lastname combinations may not be totally unique. Note, you may also want an address table to store multiple:

table: user
userID          int
userFirstName   varchar(20)
userLastName    varchar(20)
userEmail       varchar(100)

Data for each auction. AuctionID would be a auto-generated ID and the Primary Key. You would also want foreign keys on realmID (realmID on realm table) and sellerID (userID on user table):

table: auction
auctionID       int
realmID         int
sellerID        int
auctionPrice    decimal
auctionStart    datetime

Data for each auction that has been completed with a foreign key on buyerID (userID on user table):

table: auctionHistory
auctionID       int
buyerID         int
finalPrice      decimal
auctionFinished datetime

I don't think that you'd need a seperate table for "Scan". That would be a simple query of rows in the auction table that do not have matching rows in the auctionHistory table. Hopefully, this will help you get started. If I missed/misread anything, please let me know.

Sql-server – Which of these table designs is better for performance

A billion records a year isn't much.

With partitioning (per Costtype maybe) and archiving it is manageable.

The number of data items to store is still 200k * 13 * N. As columns, you'll get less rows per page and it will take more space than as rows. You may gain if "CostType1" is not a fixed length datatype, but it's marginal.

"KISS" as they say

Best Answer

Related Solutions

MySQL database design

Sql-server – Which of these table designs is better for performance

Related Question