Database Dilemma... Please help.

Gregory Hill Gregory_Hill at
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. 


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

More information about the PLUG mailing list