SQL QUERY Help Requested
I'm wondering if someone would be kind enough to look at the following query
and shed some lite on what's happening and what I need to do to force/trick
it into running the query the quick way.
I've analyzed using sqexplain and discovered it's running the query
differently depending on how many items are in the qual qualifier. (We're
running Online 7.11, so directives are not yet an option.)
SELECT a.location, a.unique_no, a.sys_code, a.prod_code, b.cust_acct_no,c.qual, d.dest_ind, d.dest, d.attn
FROM table1 a, table2 b, table3 c, OUTER table4 d
WHERE a.status = 'A' AND
c.qual in ( 'Q1' , 'Q2') AND
b.group_code = 'GROUP1' AND
a.unique_no = b.unique_no AND
b.cust_acct_no = c.cust_code AND
d.cust_code = c.cust_code
If there are more than two members to the qual field the query runs very
quickly and the selection order is:
a. status,
b.group_code,
c.qual,
d.cust_code
When there are one or two members the query is extremely slow and the
selection order is:
c.qual,
b.group_code,
a.status,
d.cust_code.
Obviously, I'd like to force/trick it into running the quick way we're
talking two vs thirty-two seconds.
Below are the indexes that are currently on the table.
Indexes:
table1 (status);
table1 (unique_no);
table1 (sys_code);
table1 (location);
table1 (date_received);
table1 (sys_code,prod_code);
table2 (unique_no);
table2 (unique_no,ves,cust_acct_no);
table2 (group_code);
table2 (cust_acct_no);
table3 (cust_code,type);
table3 (cust_code);
table3 (qual);
table4 (cust_code);
Any help will be greatly appreciated.
TIA,
Carol