MySQL Sharding

Databases
1

Hello

Suppose I have the following 2 tables in a non-sharded cluster.


CREATE TABLE `users` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(64) NOT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1;

CREATE TABLE `userprofile` (
  `profileid` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`profileid`)
) ENGINE=MyISAM AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1;

And I use the following query to get the results from these 2 tables:

SELECT users.*, userprofile.* from users, userprofile where users.userid = userprofile.userid LIMIT 10000

What changes do I have to make in my above query if I want to put the database in a sharded environment, for example if I want one table in one shard and second table in another OR vertically distribute the data of the tables in diff shards?

Any help will be appreciated.

PS: I will be implementing this in a PHP project so if mentioning that makes a difference in the query, do let me know

Thanks

2

Sharding is the new hype name for replication and should be fully transparent to the application. Your application connects to “the database” and just runs it’s queries. What the database does internally to solve the query whould be completely hidden from the application. So, I shouldn’t think that you’d have to change anything at all.

3

Not even if the tables are distributed on diff servers? In such case will JOINS work properly without doing any modification in teh code?

4

Sharding doesn’t put tables on different servers, it spreads all the data equally across several servers. See http://en.wikipedia.org/wiki/Shard_(database_architecture)