Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Unanswered: Query Optimization

    Query:
    SELECT masterquotes.webquotecontrol, usercontrol, username,
    shipname, shipcity, shipstate,
    consname, conscity, consstate, estdeldate, deldate,
    puavaildt1, puavaildt2, masterquotes.statusedi, process,
    status.statuspriority, masterquotes.carrierid, enteredOTHER, hardcopy,
    cs.setlrepid, cs.clientnet, cs.provider_approved,
    destavaildt1, destavaildt2, guarddel, masterquotes.conszip, cs.pronum as pronum, cs.readysettle, truckload, assigned_to, rating_issue,
    need_po, multiple_sps, approval_needed, billto_disc, refund_due, investigate
    FROM masterquotes use index(idx_main), carriersettlement as cs use index(idx_general), status force index(idx_main)
    WHERE dtEntered like '2006-04-26%' and masterquotes.statusedi!='Pend-I' and
    (masterquotes.statusedi!='Memo' and masterquotes.statusedi!='MEMO' and masterquotes.statusedi!='NOT SRCS' and masterquotes.statusedi!='NOT SRCS-I' and masterquotes.statusedi!='PEND-I' and masterquotes.statusedi!='Pend-A') and
    status.statusedi=masterquotes.statusedi and masterquotes.webquotecontrol=cs.webquotecontrol

    Indexes:
    Masterquotes KEY `idx_main` (`dtEntered`,`statusedi`)
    Status= KEY `idx_main` (`statusedi`,`statuspriority`)
    Carriersettlement = KEY `idx_general` (`webquotecontrol`)

    Explain:
    +----+-------------+--------------+------+------------------+-------------+---------+--------------------------------------------+-------+--------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------------+------+------------------+-------------+---------+--------------------------------------------+-------+--------------------------+
    | 1 | SIMPLE | masterquotes | ALL | PRIMARY,idx_main | NULL | NULL | NULL | 39475 | Using where |
    | 1 | SIMPLE | cs | ref | idx_general | idx_general | 50 | shippersprice.masterquotes.webquotecontrol | 1 | Using where |
    | 1 | SIMPLE | status | ref | idx_main | idx_main | 10 | shippersprice.masterquotes.statusedi | 3 | Using where; Using index |
    +----+-------------+--------------+------+------------------+-------------+---------+--------------------------------------------+-------+--------------------------+

  2. #2
    Join Date
    Apr 2006
    Posts
    2

    Question

    My Question is why is it not using my index for the masterquotes table??

Posting Permissions

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