

Basic MySQL Performance Tuning Practices that Everyone Should Know About - pkrumins
http://www.catonmat.net/blog/performance-tuning-best-practices-for-mysql/

======
cbrinker
Oh how many businesses spend countless thousands on incompitent contractors
when all they needed was someone to simply run EXPLAIN.

~~~
ajross
And who's going to interpret that output if all they have is incompetent
contractors?

The problem with database optimization isn't that "people don't understand it
well", it's that database optimization is _inherently_ _obscure_ and
_difficult_. With most code, performance is a local effect: you can look at
the code and see why it's slow. With some harder problems, performance is
data-dependent and you need to look very closely.

With databases, it's worse still: performance is a feature of the _schema_ ,
_configuration_ , and (worse still) the quirks of the software (ex: I never
knew MySQL can't optimize count(*) queries!) . They can be useful, but they're
hugely complicated. In general, using a database without an expert DBA is a
recipe for poor scalability.

~~~
mdasen
For the count(*) queries:

Almost no database optimizes this. What they do is count the rows. MyISAM (one
of the storage engines for MySQL) heavily optimizes this by having a row count
in the table data.

This turns out not to be that useful because who wants to count the number of
rows in a table? You usually want to count a subset of those rows. Like, how
many comments are there for post_id 7.

So, MySQL isn't unoptimized for this.

~~~
ajross
You mean MySQL can give you the answer in constant or log time. But folks like
me, who naively use the count() aggregate like we've been taught in books, are
going to have scalability problems unless we understand the internals of the
database engine.

That was my point: it's not a slam against MySQL per se, it's a slam against
the whole development metaphor, which seems to derive unholy pleasure in
hiding critically important implementation details from the developer.

------
patrickg-zill
Step 1. Install PostgreSQL .

~~~
cbrinker
Both MySQL and PostgreSQL are absolutely fantastic pieces of software. Both
have their pros and cons, but in general, they are neck in neck.

There was a nice quote that I forget where I read it, but it goes as such:

"Choose MySQL if you want an open source product, PostgreSQL if you want an
open source project."

~~~
newt0311
Actually no. PostgreSQL and MySQL are certainly not neck to neck especially in
performance. See <http://tweakers.net/reviews/657>

Summary: MySQL comes close to beating PostgreSQL (in speed) when there is
little contention but gets absolutely clobbered when there are multiple users
simultaneously.

My addition: Even with 5.1, MySQL is still severely lacking when it comes to
ANSI features as compared to PostgreSQL and other commercial solutions like
Oracle and DB2.

