Subject: Re: MySQL Optimization Assistance
From: Troy Melhase (troy@gci.net)
Date: Mon Nov 10 2003 - 15:46:29 AKST
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Monday 10 November 2003 15:14, Joshua Kugler wrote:
> 1. Make sure you have indexes on all the columns in your WHERE clauses
More specifically, make sure you have an index for each group of columns in
the WHERE clause(s). There's a difference between two indexes on two columns
and one index on two columns. If the queires are from joined tables, the
columns involved in the JOIN should be examined as well.
> 2. Try not to use TEXT/BLOB fields or VARCHAR fields unless you really,
> really need to
There shouldn't be harm in either of these data types as long as they're not
part of the WHERE clause in frequently used queries.
> 3. If you need to be able to search on ANY field in the table, and you need
> speed, index them all: it'll take lots of disk space, but the time payoff
> will be worth it
And UPDATE and INSERT performance will suffer. The OP didn't qualify what he
or she meant as "running slower", and it could have just as easily been
"running slower" from bulk inserts or updates.
> I can't really comment any further without seeing the layout of your
> database. It is possible it needs some normalization, or other
> optimization.
Normalization and optimization are often opposing forces, i.e.,
over-normalization can lead to poor query performance.
My advice would be to trace the execution plan of the queries involved, and
use the results to create the indexes. Dunno if such a beast exists for
mysql.
I'm sorry if I sound contentious -- that's not my intent. I do think it's
necessary to avoid these kinds of blanket generalizations, however.
- --
Troy Melhase, troy@gci.net
- --
Any sufficiently advanced technology is indistinguishable from Python.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQE/sDFxPPgja6MkvqMRApw/AJ0Rpz/f8QGGG6bDZFZW7ydRUGgCAACfaJ7Z
EiICL4rrFw8Cv5A29CxaGmY=
=KpKr
-----END PGP SIGNATURE-----
---------
To unsubscribe, send email to <aklug-request@aklug.org>
with 'unsubscribe' in the message body.
This archive was generated by hypermail 2a23 : Mon Nov 10 2003 - 15:46:40 AKST