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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of > Sasha Pachev > Sent: Friday, April 07, 2006 10:22 AM > To: [email protected] > 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. > */ /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
