Re: [Nolug] MySQL Help?

On Sat, 2004-05-29 at 00:54 -0400, Mark A. Hershberger wrote:
> I've got a MySQL question for the gurus out there.
>
> A table periodically locks up (no one can query it). Running strace
> on the existing mysql processes that are attempting to access the
> table (using "fuser /db/database/table.MYD") shows one process
> processing a lot of data and all the others evidently blocking while
> they wait on that process to complete.
>
> Prior to my changes, these problems didn't occur. After I added a
> query to the queue-running cronjob that did a table join (no table
> updates) the above symptoms started happening about once a day.
>
> The query was changed from:
>
> SELECT * FROM tblData WHERE 1=1 AND ...
>
> to
>
> SELECT DISTINCT tblData.* FROM tblData,zipDistances WHERE
> zip=zip_distant AND ...

What do the indexes, if any, on tblData look like?

Also, remember that DISTINCT adds computational and disk
overhead to the operation, since it has to *sort* all the
records in the table, in order to eliminate duplicates.

<ADVOCACY mode=FLAMEBAIT>
This is why you should use a real RDBMS like PostgresSQL,
rather than a hacked-up ISAM like MySQL.
</ADVOCACY>

-- 
Ron Johnson <ron.l.johnson@cox.net>
___________________
Nolug mailing list
nolug@nolug.org