Adam Douglas writes:
> Correct 6 tables are being joined. Here's the explain of the query below
> (btw, I'm not getting a index on CompanyShipInfo and PostalCodeInfo not
> CompanyRep like I previous said). Excuse me if this seems dumb but I'm not
> sure what you are saying in the last two comments. I understand an index
> could be missing but where? I'll put the indexing/schema below the explain
> for you. Basically I need to get the query to run faster. I have three
> queries one for our Applied Product, Unitary Product and Wholesalers. A user
> will send to the query a US Zip Code and based on that zip code it will
> return the representative(s) that looks after that area (according to zip
> code) for each product line. It takes about 14 seconds to run this query and
> two others via PHP. Now this seems to vary and sometimes renders mysqld
> useless. Mysqld will not accept at times user logins or even checking for
> database status. The CPU usage on this process when it doesn't work goes
> from 99.0% to 99.02%. It doesn't seem to matter how long you leave it, never
> seems to end. Now the biggest tables I have is PostalCodeInfo and
> PostalCodeReps. Both contain about 42657 rows, so table size is not a
> problem.
>
<cut>
If you scrutinize more closely your query and your table you can
notice that :
- all tables are not related with common columns
- There are two indices missing
- MySQL is using all of the indices available except on some small
tables, like CompanyShipInfo
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sinisa@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
