Closed Thread Icon

Topic awaiting preservation: MySQL efficiency Pages that link to <a href="https://ozoneasylum.com/backlink?for=11929" title="Pages that link to Topic awaiting preservation: MySQL efficiency" rel="nofollow" >Topic awaiting preservation: MySQL efficiency\

 
Author Thread
jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 11-26-2001 16:12

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

GRUMBLE
Paranoid (IV) Mad Scientist

From: Omicron Persei 8
Insane since: Oct 2000

posted posted 11-26-2001 16:40

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).]

InI
Paranoid (IV) Mad Scientist

From: Somewhere over the rainbow
Insane since: Mar 2001

posted posted 11-26-2001 17:25

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.

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 11-26-2001 17:28

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

bitdamaged
Maniac (V) Mad Scientist

From: 100101010011 <-- right about here
Insane since: Mar 2000

posted posted 11-26-2001 22:06

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. ]:

Emperor
Maniac (V) Mad Scientist with Finglongers

From: Cell 53, East Wing
Insane since: Jul 2001

posted posted 11-26-2001 22:24

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

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 11-26-2001 23:06

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

Pugzly
Paranoid (IV) Inmate

From: 127.0.0.1
Insane since: Apr 2000

posted posted 11-27-2001 01:54

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.

« BackwardsOnwards »

Show Forum Drop Down Menu