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
> )
Hi!
You have 6 tables in a join.
Possible causes of not using indices could be some index missing or
low number of rows.
Beside that, you do have Cartesian product as there are some tables
not related.
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sinisa@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
