Hi!
>>>>> "Sinisa" == Sinisa Milivojevic <sinisa@stripped> writes:
Sinisa> Adam Douglas writes:
>> I'm running MySQL v3.22.32. Here's my problem I run the following
>> select query and both CompanyRep and PostalCodeInfo tables are not
>> using my Indexing. Why? How can I get these two tables to use indexing
>> to speed up my query. You can look at my indexing on the two tables
>> below the query.
>>
>> Any help would be greatly appreciated, thanks!
>>
>> 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
>> )
Sinisa> Hi!
Sinisa> You have 6 tables in a join.
Sinisa> Possible causes of not using indices could be some index missing or
Sinisa> low number of rows.
Sinisa> Beside that, you do have Cartesian product as there are some tables
Sinisa> not related.
Try running an EXPLAIN for the query to verify which indexes are used.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <monty@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
