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 |
+----------------+------------+---------------+--------------+--------------
-------+-----------+-------------+----------+
