Database Dilemma... Please help.
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.
> -----Original Message-----
> From: plug-bounces at plug.org [mailto:plug-bounces at plug.org] On Behalf
> 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
> >performance. After that comes proper indexing and optimized queries.
> I strongly disagree about the order. Proper queries and indexing are
> important than hardware. A full join of three 1,000,000 record tables
> much slower on the fastest hardware there is than the same query on an
> with 32 MB RAM if you have the right keys to eliminate the full join
> Sasha Pachev
> AskSasha Linux Consulting
> Running Blog
> 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