1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Multiple Database vs Single Database

Discussion in 'MySQL' started by Aragorn, May 16, 2007.

  1. #1
    Can somebody plz tell me which is better. Creating different database for different sections of my site or creating all the tables in a single database?
    I am developing a new website and it will have many subsections like directory, yellowpages, userlogin etc. All of which will be using mysql database. Is there any advantage / disadvantage if I create different database for each sections? Thanks :)
    SEMrush
     
    Aragorn, May 16, 2007 IP
    designcode likes this.
    SEMrush
  2. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #2
    If some modules on my projects are independent and usable for different projects, at that time I prefer creating different databases for them. So deployment of that modules becomes easy.
    Otherwise I am using one database but I am using prefixes for my tables like;
    directory_data, directory_lookup, users_data, yp_pagedata etc..
     
    Clark Kent, May 16, 2007 IP
    Aragorn likes this.
  3. Aragorn

    Aragorn Peon

    Messages:
    1,491
    Likes Received:
    72
    Best Answers:
    1
    Trophy Points:
    0
    #3
    Thanks for the reply. Will using a single database, affect the speed?
     
    Aragorn, May 16, 2007 IP
  4. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Nope I never see that but If you have more than 300+ tables, some frontend GUI's opens slow because they check every tables status (row counts etc).
     
    Clark Kent, May 16, 2007 IP
  5. Aragorn

    Aragorn Peon

    Messages:
    1,491
    Likes Received:
    72
    Best Answers:
    1
    Trophy Points:
    0
    #5
    Thanks a lot Clark. I think I should go for single Db.
     
    Aragorn, May 17, 2007 IP
  6. Mr D

    Mr D Peon

    Messages:
    35
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    a single db is easier to work with, then you don't need many connections in one script
     
    Mr D, May 17, 2007 IP
    Aragorn likes this.
  7. designcode

    designcode Well-Known Member

    Messages:
    738
    Likes Received:
    37
    Best Answers:
    0
    Trophy Points:
    118
    #7
    I will recommend a single database, even if you have a big application. Single database is easy to maintain and all your data layer is on a single place.

    Expose modules on webservices if you want to use a single db for multiple applications. Making multiple databases is no good thing.
     
    designcode, May 17, 2007 IP
    Aragorn likes this.
  8. everypcneed

    everypcneed Peon

    Messages:
    238
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #8
    You will also want to consider how active each area of the site will be and how many calls to the database will be made in a short period of time with taking into account the speed of the database host to return the requested details.

    With the above you want to ensure your visitors are receiving the information quickly without lag. If you think each area of the site will be quite busy consistantly it may be a better idea to assign each component its own database to avoid latency issues.
     
    everypcneed, May 17, 2007 IP
    Aragorn likes this.
  9. lorzlover

    lorzlover Peon

    Messages:
    46
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    Well, if you create multiple databases, you'll get it easy to backup them. (because they are separated in small files)
    If you have a single database, you site loads faster but you will have to wait longer to create a backup.
     
    lorzlover, May 17, 2007 IP
  10. Clark Kent

    Clark Kent Guest

    Messages:
    122
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #10
    You don't have to open many connections.
    Suppose you have 3 different databases, db1, db2, db3.

    When you connect one of them you can access 3 of them (if you gave access or you are using root account).

    Sample:
    Select t1.field1, t2.field1, t3.field1
    from db1.table1 t1
    left join db2.table1 t2 on t2.field1=t1.field1
    left join db3.table1 t3 on t3.field1=t2.field1
     
    Clark Kent, May 19, 2007 IP
  11. Aragorn

    Aragorn Peon

    Messages:
    1,491
    Likes Received:
    72
    Best Answers:
    1
    Trophy Points:
    0
    #11
    Oh yes, I always forget that and open separate connections instead.
     
    Aragorn, May 19, 2007 IP