SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: MySQL Sharding

  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL Sharding

    Hello

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

    Code:
    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:

    Code:
    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. #2
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    752
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sharding doesn't put tables on different servers, it spreads all the data equally across several servers. See http://en.wikipedia.org/wiki/Shard_(..._architecture)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •