Hi Chris,

That's a great question that is going to be hard to answer on a general basis as it is implementation-specific (database server, database engine, memory, and hard drive speed).

To do a gross oversimplification, if you have a single database server and you get a random mix of traffic from all of the sites, then having everything in one database and using your 'org_id' should be faster since you will have potentially fewer files open and the database has fewer indexes to cache. But each database server and database engine handles table-to-file mapping and caching differently, so your mileage may vary.

It would probably be very worthwhile to write some test applications to simulate queries to the database and get some numbers for your particular setup. With a little tuning of the database cache and memory settings for a particular usage scenario, you can substantially increase the performance.

To give you an idea of performance increases, I have some databases with over 900,000,000 records and just rebuilding the indexes took around 3 days with MySQL and MyISAM. I checked the memory usage and the darned thing wasn't using even half of the memory it could. It turned out that I needed to tweak the index (key) buffer settings which reduced the rebuild time to less than 8 hours. Some of the newer database engines like InnoDB do a much better job with file and memory management so you don't have to tweak as much. I've heard that other databases such as PostGRES are much better, but I don't have any first-hand experience to comment to that.

Hope that helps somewhat.

-Eric