Database Dilemma... Please help.

Gregory Hill Gregory_Hill at tni.com
Fri Apr 7 10:40:53 MDT 2006


I totally agree.  Improper indexing was making a query of mine take >15
minutes to run (sometimes it would even crash the server).  Once I
figured out the problem, I fixed the index, and the same exact query on
the exact same box took <1 second to run.  And that was on a table with
only a few hundred thousand rows (on mysql 3.23, if you're curious).

The problem was my fault.  I had UNIQUE KEY(account_id, company_id) and
was joining only on account_id.  I assumed that it could join on either
part of the two-field index as if it were a separate index, but I was
wrong (ah, the naivety). It had to do a full table scan every time.
Creating a separate index for account_id and company_id fixed the
problem.  It was quite a few years back and a valuable lesson to learn,
although I would've preferred learning it by reading about it :)

So, it's not just that you need to index things, you need to index the
right things.  EXPLAIN is your best friend. 

Greg

> -----Original Message-----
> From: plug-bounces at plug.org [mailto:plug-bounces at plug.org] On Behalf
Of
> Sasha Pachev
> Sent: Friday, April 07, 2006 10:22 AM
> To: plug at plug.org
> Subject: Re: Database Dilemma... Please help.
> 
>  >But still, hardware is the biggest issue when it comes to any
database
>  >performance. After that comes proper indexing and optimized queries.
> 
> I strongly disagree about the order. Proper queries and indexing are
much
> more
> important than hardware. A full join of three 1,000,000 record tables
will
> run
> much slower on the fastest hardware there is than the same query on an
old
> 486
> with 32 MB RAM if you have the right keys to eliminate the full join
> aspect.
> 
> --
> Sasha Pachev
> AskSasha Linux Consulting
> http://www.asksasha.com
> 
> Running Blog
> http://sasha.fastrunningblog.com
> 
> 
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */



More information about the PLUG mailing list