I have a group of 3 queries that's used to find our representative for
Unitary, Applied and Wholesale based on a Zip Code. This all is executed
through PHP to MySQL. The three queries work fine and get the results I
want. The problem is the 3 queries seem to work one minute but not the next.
You could enter say 3 or 4 zip codes and have it return results with no
problem, but on the 5 try it will crap out. This seems to be random, one
minute it wouldn't work on the first try and then the next it will. When the
3 queries crap out it renders MySQL useless, you can send or do any thing to
MySQL when the 3 queries crap out. It puts my CPU usage to 99.0% and stays
roughly there forever. I've left the query running for more then 8 hours and
still nothing seems to be returned. Can anyone direct me in the right
direction on how to resolve this problem?
Now I'm not sure if this would happen if I sent the queries directly to
MySQL, as I am not aware of a way to send all three queries one after
another (no delay). Oh also I've noticed that MySQL seems to lose connection
and tries to reconnect. This too happens randomly. Losing connection seems
to happen when I run the queries directly in MySQL (one query at a time). I
don't believe this would be a PHP problem and that is why I've posted this
message here.
Here's the three queries...
SELECT
Company.Company_Name,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
CompanyShipInfo.Ship_Postal_Code,
Countries.Country_Name,
ProvincesStates.Prov_State_Name
FROM
PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesSta
tes,CompanyRep
WHERE
(
PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID
AND PostalCodeInfo.Postal_Code = "12110"
AND Company.Company_ID = PostalCodeReps.Company_ID_Unitary
AND Company.Company_Status_ID = 1
AND (Company.Company_Type_ID = 12
OR Company.Company_Type_ID = 14)
AND CompanyShipInfo.Company_ID = Company.Company_ID
AND Countries.Country_ID = CompanyShipInfo.Country_ID
AND ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID
AND CompanyRep.Company_ID = Company.Company_ID
AND CompanyRep.Invoice_Only = 0
)
SELECT
Company.Company_Name,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
CompanyShipInfo.Ship_Postal_Code,
Countries.Country_Name,
ProvincesStates.Prov_State_Name
FROM
PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesSta
tes,CompanyRep
WHERE
(
PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID
AND PostalCodeInfo.Postal_Code = "12110"
AND Company.Company_ID = PostalCodeReps.Company_ID_Applied
AND Company.Company_Status_ID = 1
AND (Company.Company_Type_ID = 12
OR Company.Company_Type_ID = 14)
AND CompanyShipInfo.Company_ID = Company.Company_ID
AND Countries.Country_ID = CompanyShipInfo.Country_ID
AND ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID
AND CompanyRep.Company_ID = Company.Company_ID
AND CompanyRep.Invoice_Only = 0
)
SELECT
Company.Company_Name,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
ProvincesStates.Prov_State_Name,
Countries.Country_Name,
CompanyShipInfo.Ship_Postal_Code,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page
FROM
CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, ProvincesStates,
Countries
WHERE
(
CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID
AND PostalCodeInfo.Postal_Code=98188
AND CompanyRep.Company_ID=Company.Company_ID
AND CompanyShipInfo.Company_ID=Company.Company_ID
AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID
AND CompanyShipInfo.Country_ID=Countries.Country_ID
AND Company.Company_Status_ID=1
AND CompanyRep.Invoice_Only=0
AND CompanyRep.Wholesale_Rep=-1
)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
