Re: [Nolug] MySQL Help?

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`)

> 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.

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

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

I totally agree. I plan to move this app to Postgres soon, but right
now I have to deal with this.

It gets worse. Instead of storing social security numbers in a
uniform format, queries check to see if the SSN is stored in one of a
number of formats. The same with dates (which are stored as varchar
fields).

So, yes, there is poor design here, but I still don't think that this
should cause the problem I'm seeing.

Mark.

-- 
A choice between one man and a shovel, or a dozen men with teaspoons
is clear to me, and I'm sure it is clear to you also.
    -- Zimran Ahmed <http://www.winterspeak.com/>
___________________
Nolug mailing list
nolug@nolug.org