> Adam Douglas writes:
> > 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.
> >
>
> If you scrutinize more closely your query and your table you can
> notice that :
>
> - all tables are not related with common columns
>
> - There are two indices missing
>
> - MySQL is using all of the indices available except on some small
> tables, like CompanyShipInfo
Ahh what do you mean "all tables are not related with common columns"? They
are not suppose to be related with a common columns. PostalCodeInfo and
Company table are completely two different things.
Yes that is correct, I've resolved the issue with PostalCodeInfo for
indexing in the query. I had column types not matching the same through my
tables. But I'm still lost as to why I can not get CompanyShipInfo to use
indexing in the query.
That's find but shouldn't I still be able to go down even further as to how
many rows are analyzed? CompanyShipInfo has 235 rows and all are being read
by the query. Here's the new explain of the query below.
Another thing that happens is when I tried to ALTER PostalCodeInfo and
PostalCodeReps table schemas MySQL seemed to be hung and put the CPU usage
at 99.0% to 99.02% (never ending it seems). Both those tables only have
42657 rows each. Any ideas why these two tables seem to hang when anything
major is done on them? Funny thing is it seems to be random when you execute
a query that uses these two tables. Is there something I'm missing for MySQL
configuration or bad table design? I'll post the table schema's below the
explain query.
Explain of Query
+-----------------+--------+---------------------------------------+--------
-------+---------+-------------------------------+------+------------+
| table | type | possible_keys | key
| key_len | ref | rows | Extra |
+-----------------+--------+---------------------------------------+--------
-------+---------+-------------------------------+------+------------+
| CompanyShipInfo | ALL | Company_ID,Prov_State_ID,Country_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 | ref | Prov_State_ID,Postal_Code |
Prov_State_ID | 2 | CompanyShipInfo.Prov_State_ID | 223 | where used
|
| 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)
PostalCodeInfo 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 | smallint(4) | | MUL | 0 |
|
| Country_ID | smallint(3) | | MUL | 0 |
|
| Postal_Code | varchar(10) | | MUL | |
|
| County | varchar(50) | YES | | NULL |
|
+---------------------+--------------+------+-----+---------+---------------
-+
6 rows in set (0.00 sec)
PostalCodeReps Schema
+---------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| Postal_Code_Rep_ID | int(11) | | PRI | 0 | |
| Postal_Code_Info_ID | mediumint(9) | | MUL | 0 | |
| Company_ID_Unitary | mediumint(9) | | MUL | 0 | |
| Company_ID_Applied | mediumint(9) | | MUL | 0 | |
+---------------------+--------------+------+-----+---------+-------+
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
