CODEWITHSUNDEEP
databasedrupal
Summer Nguyen
Summer Nguyen

Reputation: 229

Multi Database on Drupal

I have to implement a system that contain multi-sites using Drupal. ( expected 1000 sites) Drupal allow us to use multi databases or one database with prefix. With thousands of sites, I may choose multi databases instead of one database with prefix. And All databases have same table structure. But the problem is : There are some very frequently query that collect information from all sites ( all databases). For example: Select 20 products have category is Shoes and has good voting rate from all databases. I have 3 solutions right now :

  1. Use a for loop to select databases . it means 1000 query "Select * from db1.table1 where category=123 and vote=10"; And then choose matching product. I think that's worse in performance because there are so much memory used if there are many rows in all databases.
  2. Use Select * FROM db1.table1 .... UNION SELECT * FROM db2.table1 ....... I think that's bad too. Because mysql will choose from db1 first, if there is enough 20 products, mysql will stop finding.
  3. Whenever inserting to all database, we insert into a very large database with a site flag. So that, we can do select query from this very large databases using a simple query : "SELECT * FROM largeDB.table WHERE category=123 and vote=10".

Please give me more advices . Thank you very much. P.S: And please tell me more about problems with database maintain when I have 1000s same databases.

Upvotes: 2

Views: 527

Answers (1)

TelFiRE
TelFiRE

Reputation: 472

I just want to make sure you are aware of Drupal's out-of-the-box multisite functionality? All you have to do is make a new folder in the sites folder, and either create a symbolic link to it and/or add it to the sites.php (which may or may not work depending on server).

Sorry if you already knew this, wanted to make sure you don't custom do a solution if this will work for you. It handles multiple sites with their own databases on a single Drupal install quite well. Unfortunately I can't help with the multiple database queries from a single site, in the past I have just done so with a manual PHP query.

Upvotes: 1

Related Questions