Topic awaiting preservation: MySQL efficiency

Does anyone know about MySQL efficiency, and whether it's a good idea to make separate databases for unrelated tables? From an administration standpoint, it's a lot easier to give a user a single database, but does that limit the efficiency if there are many large unrelated tables within the database?

I'm planning on running some tests to explore this, so I'll post some results here later.

-jiblet

if it's possible i would keep them all in one db.
you have to connect only once.

[This message has been edited by me (edited 11-19-2001).]

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

That's not an issue. I'm talking about groups of tables that are for separate applications. I do have them all in one database now because my sysadmin has only set up one database, but performance is a big issue. My main web application at http://www.coffman.umn.edu/insider/calendar.php already takes 20 seconds to run initially (I have a caching system so people rarely experience that delay), and I wonder if the large number of tables in the database is negatively affecting performance.

-jiblet

I don't think the sheer number of tables should affect speed so much. I'm really no expert on this and should just shut up.

The one piece of advice I saw once and have stuck with is that any field used in a where clause should be an index.




:[ Computers let you make more mistakes faster than any other invention in human history, with the possible exceptions of handguns and tequila. ]:

jiblet: There may be a small performace drop from having lots of tables but as long as you are using them efficiently I can't see if there is too much of a problem. How many tables get involved in one query? I also wonder if the performance mistake is due to some coding problem (I know I stupidly once put the DB connection inside a loop and that slowed things down nicely) - just a thought.

Emps


You're my wife now Dave

Yeah, I'm pretty sure there is some kind of stupid mistake because it takes 40 seconds to list 100 events when there are no search criteria, but it takes less than 2 seconds if all the sponsors are checked resulting in the same 100 events. Weird, eh?

Anyway, as far as that goes I'll have to figure it out myself, because the code is way too complicated to ask anyone here to solve a deep problem with.

-jiblet

I've lived the "one site - one db" rule, and it's worked out well. I NEVER have tables for more than one site in the same DB. And more than one DB makes for more connections, which, is bad - as everyone has mentioned.

If you use something like phpmyadmin, having things all in one db is a little easier as well.