> Adam Douglas writes:
> > Ahh what do you mean "all tables are not related with
> common columns"? They
> > are not suppose to be related with a common columns.
> PostalCodeInfo and
> > Company table are completely two different things.
> >
> > Yes that is correct, I've resolved the issue with PostalCodeInfo for
> > indexing in the query. I had column types not matching the
> same through my
> > tables. But I'm still lost as to why I can not get
> CompanyShipInfo to use
> > indexing in the query.
> >
> > That's find but shouldn't I still be able to go down even
> further as to how
> > many rows are analyzed? CompanyShipInfo has 235 rows and
> all are being read
> > by the query. Here's the new explain of the query below.
> >
> > Another thing that happens is when I tried to ALTER
> PostalCodeInfo and
> > PostalCodeReps table schemas MySQL seemed to be hung and
> put the CPU usage
> > at 99.0% to 99.02% (never ending it seems). Both those
> tables only have
> > 42657 rows each. Any ideas why these two tables seem to
> hang when anything
> > major is done on them? Funny thing is it seems to be random
> when you execute
> > a query that uses these two tables. Is there something I'm
> missing for MySQL
> > configuration or bad table design? I'll post the table
> schema's below the
> > explain query.
>
> MySQL is truly doing it's best in your case.
>
> If of 6 tables you have a broken chain of relations, you will have a
> Cartesian product.
>
> Regarding ALTER TABLE, that is also expected behaviour, as MySQL is
> re-building indices and has to use lot's of CPU.
Alright, but is there no way then to decrease my query time of 14 seconds
(includes 3 queries) to something more reasonable for use on-line? 10
seconds according to stats is the maximum limit.
Maybe I'm not understanding things correctly here, but I do not have a
Cartesian product since my query will only return 1 result. Cartesian is
when MySQL has to match row for row when tables do not have any relations
correct (in brief)?
Well that doesn't make sense to me, so how long should such a task take
then? I've ran it all night and still it wasn't finished, seems to be hung
not just taking to long. When you have a table with 42,000+ rows it should
take more then 8 hours to update no?
