SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    High Point, NC, USA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query locks-up database

    MySQL version 4.0.12-nt.

    I have the following table with 180,000 records. When the query below it appears to lock-up and the computer becomes very slow (as it does with any 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?
    Code:
    +----------------+------------------+------+-----+---------+----------------+
    | 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

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What exactly are you trying to do?

    What indexes are on the table?

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    High Point, NC, USA
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks for your help!
    Charles


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •