Re: MySQL Optimization Assistance

Subject: Re: MySQL Optimization Assistance
From: KSchneider (
Date: Mon Nov 10 2003 - 16:21:49 AKST

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). This will happen, perhaps,
once a month. Daily the update/append script I wrote will affect only
perhaps 200 of the 200K records (though it will search through all of
them). I fully expect the search to take longer as the update/append
script is running, but not when it has finished. Are resources consumed
and then held on to? Can I free these resources with a command
(flush-tables, for instance)?

Any advice on this?

ps. I use varchar and char where appropriate and text/blob only when
absolutely necessary (none in this case). I use two-field primary keys and
single indexes, depending upon the scripts I write. Generally I create an
index for each field in a dual primary key scenario as well (if they're a
part of a select statement somewhere).

To unsubscribe, send email to <>
with 'unsubscribe' in the message body.

This archive was generated by hypermail 2a23 : Mon Nov 10 2003 - 16:21:03 AKST