MySQL Forums
Forum List  »  Optimizer & Parser

Query Optimization
Posted by: Joe Taylor
Date: April 26, 2006 01:57PM

Why is it not using my index for the masterquotes table??

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 |
+----+-------------+--------------+------+------------------+-------------+---------+--------------------------------------------+-------+--------------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Query Optimization
3723
April 26, 2006 01:57PM
2004
April 26, 2006 04:46PM
1900
April 26, 2006 04:56PM
2087
April 26, 2006 05:29PM
1890
May 03, 2006 03:11PM
1885
May 03, 2006 03:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.