Re: [Nolug] MySQL Help?

On Sat, 2004-05-29 at 14:14 -0400, Mark A. Hershberger wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
>
> > What do the indexes, if any, on tblData look like?
>
> PRIMARY KEY (`id`),
> KEY `idusername` (`id`,`username`),
> KEY `zip` (`zip`(5)),
> KEY `apptypezip` (`zip`(5),`apptype`(7)),
> KEY `username_idx` (`username`(10))
>
> on zipDistances
>
> KEY `origdist` (`origin`,`distance`),
> KEY `zip` (`zip_distant`)

Since the OP didn't have the full SELECT statement, I
can't help much more, since you only mentioned the
1st predicate.

Besides, while I'm a DBA, I don't know the internals
of MySQL, to tell you how *it* would act in a given
situation. Sorry.

> > 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.
>
> Yes, but it is still /far/ faster than the really huge "zip in" that
> we were using before.

WHERE zip in (SELECT zip from zipDistances...) ?

I should hope so! That's what INNER JOIN is for.

> And, it shouldn't lock the table, right?

Well, I've heard that table locking has traditionally
been MySQL's favorite kind of locking, from it's roots
where it was weighted towards read-heavy environments.

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