> -----Original Message-----
> From: Sinisa Milivojevic [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 31, 2001 1:27 PM
> To: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]
> Subject: Re: Query Not Using Indexing
>
>
> 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.
Correct 6 tables are being joined. Here's the explain of the query below
(btw, I'm not getting a index on CompanyShipInfo and PostalCodeInfo not
CompanyRep like I previous said). Excuse me if this seems dumb but I'm not
sure what you are saying in the last two comments. I understand an index
could be missing but where? I'll put the indexing/schema below the explain
for you. Basically I need to get the query to run faster. I have three
queries one for our Applied Product, Unitary Product and Wholesalers. A user
will send to the query a US Zip Code and based on that zip code it will
return the representative(s) that looks after that area (according to zip
code) for each product line. It takes about 14 seconds to run this query and
two others via PHP. Now this seems to vary and sometimes renders mysqld
useless. Mysqld will not accept at times user logins or even checking for
database status. The CPU usage on this process when it doesn't work goes
from 99.0% to 99.02%. It doesn't seem to matter how long you leave it, never
seems to end. Now the biggest tables I have is PostalCodeInfo and
PostalCodeReps. Both contain about 42657 rows, so table size is not a
problem.
EXPLAIN Query
+-----------------+--------+---------------------------------------+--------
----+---------+-------------------------------+-------+---------------------
-------------------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+-----------------+--------+---------------------------------------+--------
----+---------+-------------------------------+-------+---------------------
-------------------------+
| CompanyShipInfo | ALL | Company_ID | NULL
| NULL | NULL | 345 |
|
| Company | eq_ref | PRIMARY,Company_Status_ID | PRIMARY
| 3 | CompanyShipInfo.Company_ID | 1 | where used
|
| CompanyRep | ref | Company_ID,Wholesale_Rep,Invoice_Only |
Company_ID | 3 | Company.Company_ID | 1 | where used
|
| PostalCodeInfo | ALL | Prov_State_ID | NULL
| NULL | NULL | 42657 | range checked for each
record (index map: 2) |
| ProvincesStates | eq_ref | PRIMARY | PRIMARY
| 2 | CompanyShipInfo.Prov_State_ID | 1 |
|
| Countries | eq_ref | PRIMARY | PRIMARY
| 2 | CompanyShipInfo.Country_ID | 1 |
|
+-----------------+--------+---------------------------------------+--------
----+---------+-------------------------------+-------+---------------------
-------------------------+
6 rows in set (0.00 sec)
CompanyShipInfo Table Schema
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| Company_Ship_ID | mediumint(9) | | PRI | 0 | auto_increment |
| Company_ID | mediumint(9) | | MUL | 0 | |
| Ship_Addr1 | varchar(60) | YES | | NULL | |
| Ship_Addr2 | varchar(60) | YES | | NULL | |
| Ship_City | varchar(60) | YES | | NULL | |
| Prov_State_ID | smallint(2) | YES | | NULL | |
| Country_ID | smallint(3) | YES | | NULL | |
| Ship_Postal_Code | varchar(10) | YES | | NULL | |
| Notes | varchar(255) | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
CompanyShipInfo Indexing
+-----------------+------------+------------+--------------+----------------
-+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part |
+-----------------+------------+------------+--------------+----------------
-+-----------+-------------+----------+
| CompanyShipInfo | 0 | PRIMARY | 1 | Company_Ship_ID
| A | 345 | NULL |
| CompanyShipInfo | 1 | Company_ID | 1 | Company_ID
| A | NULL | NULL |
+-----------------+------------+------------+--------------+----------------
-+-----------+-------------+----------+
2 rows in set (0.00 sec)
PostalCodeInfo Table Schema
+---------------------+--------------+------+-----+---------+---------------
-+
| Field | Type | Null | Key | Default | Extra
|
+---------------------+--------------+------+-----+---------+---------------
-+
| Postal_Code_Info_ID | mediumint(9) | | PRI | 0 | auto_increment
|
| City | varchar(50) | YES | | NULL |
|
| Prov_State_ID | tinyint(4) | | MUL | 0 |
|
| Country_ID | tinyint(3) | | MUL | 0 |
|
| Postal_Code | varchar(10) | | MUL | |
|
| County | varchar(50) | YES | | NULL |
|
+---------------------+--------------+------+-----+---------+---------------
-+
6 rows in set (0.00 sec)
PostalCodeInfo Indexing
+----------------+------------+---------------+--------------+--------------
-------+-----------+-------------+----------+
| 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 |
+----------------+------------+---------------+--------------+--------------
-------+-----------+-------------+----------+
4 rows in set (0.00 sec)
---------------------------------------------------------------------
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
