Subject: Re: MySQL Optimization Assistance
From: Joshua Kugler (jk@as.uaf.edu)
Date: Mon Nov 10 2003 - 16:13:18 AKST
All very good points. If you want to find out what is going on, in a query
window, put the keyword EXPLAIN in front of any query, and it will tell you
what indexes were used, what tables were accessed, etc.
j----- k-----
On Monday 10 November 2003 03:46 pm, Troy Melhase wrote:
> -----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.
-- Joshua Kugler Assistant Systems Administrator UAF Department of Mathematical Sciences UAF LUG President --------- 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 - 16:13:18 AKST