Subject: Re: MySQL Optimization Assistance
From: Troy Melhase (troy@gci.net)
Date: Mon Nov 10 2003 - 22:57:31 AKST
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Monday 10 November 2003 16:21, KSchneider wrote:
> At 03:46 PM 11/10/2003, Troy Melhase wrote:
> >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'd like to address this point in particular, because the search results (a
> very direct select statement with only two or three joins, depending)
> started taking longer to display *after* I made a bulk insert (of just
> under 200K records each into three tables).
let me be more clear than i was originally. performance of UPDATE and INSERT
statements typically decreases with the number of indexes you have on a
table. this is because the DBMS has to recalculate the index when the data
in the table changes. we didn't know if you were talking about the
performance of your SELECT statements, or if you were talking about an INSERT
or an UPDATE.
it sounds like your problem is lack of indexes, just as Joshua pointed out.
you're adding lots of data, and now there's a lot more work for the DBMS to
do to join that data and select the subset that you want. thus, your queries
now take a much longer time to return their results.
if you can live with your bulk inserts taking longer, then by all means, add
the appropriate indexes. if performance is a consideration there, consider
dropping the indexes prior to doing a bulk insert, then reconstruct those
indexes after the insert is complete. i've had cause to use this approach
frequently at the office -- it sounds much more difficult than it actually
is.
> Are resources consumed
> and then held on to? Can I free these resources with a command
> (flush-tables, for instance)?
i can't speak to mysql -- but i can tell you that the question runs counter to
the purpose of a DBMS -- don't try to out think it! let it do it's job and
try to work within its constraints. oh, and your queries will run faster if
you don't have tables flushed to disk. :)
- --
Troy Melhase, troy@gci.net
- --
It is not as a child that I believe and confess Jesus Christ. My Hosanna is
born of a furnace of doubt. - Fyodor Dostoevsky
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQE/sJZrPPgja6MkvqMRAr5VAJ9atD351/3tkH9Qp1If+n5uJksmMgCfR2Y2
3YLgrimCxVP8mUB/eoRufwo=
=uZVa
-----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 - 22:57:32 AKST