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
> )
>
> mysql> show index from CompanyRep;
> +------------+------------+---------------+--------------+---------------+--
> ---------+-------------+----------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name
> | Collation | Cardinality | Sub_part |
> +------------+------------+---------------+--------------+---------------+--
> ---------+-------------+----------+
> | CompanyRep | 0 | PRIMARY | 1 |
> CompanyRep_ID | A | 213 | NULL |
> | CompanyRep | 1 | Company_ID | 1 | Company_ID
> | A | NULL | NULL |
> | CompanyRep | 1 | Wholesale_Rep | 1 |
> Wholesale_Rep | A | NULL | NULL |
> | CompanyRep | 1 | Invoice_Only | 1 |
> Invoice_Only | A | NULL | NULL |
> +------------+------------+---------------+--------------+---------------+--
> ---------+-------------+----------+
>
> mysql> show index from PostalCodeInfo;
> +----------------+------------+---------------+--------------+--------------
> -------+-----------+-------------+----------+
> | Table | Non_unique | Key_name | Seq_in_index |
> Column_name | Collation | Cardinality | Sub_part |
> +----------------+------------+---------------+--------------+--------------
> -------+-----------+-------------+----------+
> | PostalCodeInfo | 0 | PRIMARY | 1 |
> Postal_Code_Info_ID | A | 42657 | NULL |
> | PostalCodeInfo | 1 | Prov_State_ID | 1 |
> Prov_State_ID | A | NULL | NULL |
> | PostalCodeInfo | 1 | Country_ID | 1 |
> Country_ID | A | NULL | NULL |
> | PostalCodeInfo | 1 | Postal_Code | 1 |
> Postal_Code | A | NULL | NULL |
> +----------------+------------+---------------+--------------+--------------
> -------+-----------+-------------+----------+
>
>
> ---------------------------------------------------------------------
> 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 <mysql-thread81209@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-sinisa=mysql.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <sinisa@stripped>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
