Query locks-up database

[nevahj]

MySQL version 4.0.12-nt.

I have the following table with 180,000 records. When the query below is run it appears to lock-up and the computer becomes very slow (as it does with any complex query). I have let this query run hours without it finishing. I can run the exact same query on a much smaller table (fewer columns and only about 50 rows) and it returns results instantly. The symptoms are identical whether I am using MySQL Front with ODBC or the command line.

Does anyone know why this is happening? Is this a complex query -- too complex for MySQL?

I am trying to find all records from 2003 for each salesman where he did not sell a specific customer a specific item in 2002. The only index is on the ID field as a primary key.

+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| ID             | int(10) unsigned |      | PRI | NULL    | auto_increment |
| CUST_CODE      | varchar(100)     | YES  |     | NULL    |                |
| CUST_PO        | varchar(100)     | YES  |     | NULL    |                |
| DESC1          | varchar(70)      | YES  |     | NULL    |                |
| DESC2          | varchar(70)      | YES  |     | NULL    |                |
| discount_grp   | varchar(10)      | YES  |     | NULL    |                |
| GEN_COST       | float            | YES  |     | NULL    |                |
| GEN_PRICE      | float            | YES  |     | NULL    |                |
| INV_DATE       | date             | YES  |     | NULL    |                |
| INV_QTY        | int(11)          | YES  |     | NULL    |                |
| IN_FREIGHT     | int(11)          | YES  |     | NULL    |                |
| ITEM_CODE      | varchar(100)     | YES  |     | NULL    |                |
| LINE_NUMBER    | int(11)          | YES  |     | NULL    |                |
| MULTIPLIER     | float(13,5)      | YES  |     | NULL    |                |
| ORDER_SIZE     | int(11)          | YES  |     | NULL    |                |
| ORDER_UT       | varchar(5)       | YES  |     | NULL    |                |
| ORD_NUMBER     | varchar(100)     | YES  |     | NULL    |                |
| po_line        | int(11)          | YES  |     | NULL    |                |
| po_number      | varchar(100)     | YES  |     | NULL    |                |
| PROD_GROUP     | int(11)          | YES  |     | NULL    |                |
| pro_number     | varchar(100)     | YES  |     | NULL    |                |
| SALES_GROUP    | int(11)          | YES  |     | NULL    |                |
| SALES_LOC      | int(11)          | YES  |     | NULL    |                |
| SHIP_LOC       | int(11)          | YES  |     | NULL    |                |
| SHIP_NUMBER    | int(11)          | YES  |     | NULL    |                |
| SLM_NUMBER     | int(11)          | YES  |     | NULL    |                |
| SPEC_COST      | float            | YES  |     | NULL    |                |
| SPEC_PRICE     | float            | YES  |     | NULL    |                |
| split_comm_pct | varchar(10)      | YES  |     | NULL    |                |
| TKR_NUMBER     | int(11)          | YES  |     | NULL    |                |
| UT_COST        | float            | YES  |     | NULL    |                |
| UT_PRICE       | float            | YES  |     | NULL    |                |
| VEND_NUMBER    | varchar(100)     | YES  |     | NULL    |                |
+----------------+------------------+------+-----+---------+----------------+
33 rows in set (0.00 sec)

mysql> select count(id) from wbw_line;
+-----------+
| count(id) |
+-----------+
|    183333 |
+-----------+
1 row in set (0.06 sec)

QUERY:

select s03.*
from wbw_line s03 left outer join wbw_line s02 
  on s03.item_code = s02.item_code 
	and s03.slm_number = s02.slm_number 
	and s03.cust_code = s02.cust_code
  and year(s02.inv_date) = 2002
WHERE year(s03.inv_date) = 2003 
and s02.id is null;

Thanks in advance,
Charles

 

[sleipnir214]

I'm not suprised. Two of the columns that are the basis for your join are of type varchar(100). That tends to slow down searches.

Also, none of the columns that are the basis of your join have been indexed. Indexes tend to speed up searches.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!