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