Thanks Joe! Just what I was looking for!
On 7/7/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> SELECT DISTINCT customers.bill_state as the_result from customers
> UNION SELECT DISTINCT invoice_master.bill_state FROM invoice_master
> UNION SELECT DISTINCT customers.ship_state from customers UNION SELECT
> DISTINCT invoice_master.ship_state from invoice_master;
>
> Can anyone help speed this query up for large(r) data sets or a
> suggest a more efficient way of getting the unique list of states?
Strange... I thought DISTINCT would have used indexes if available,
but it doesn't appear to be the case:
CREATE TABLE customers(bill_state, ship_state);
CREATE TABLE invoice_master(bill_state, ship_state);
CREATE INDEX customers_bill_state on customers(bill_state);
CREATE INDEX customers_ship_state on customers(ship_state);
CREATE INDEX invoice_master_bill_state on invoice_master(bill_state);
CREATE INDEX invoice_master_ship_state on invoice_master(ship_state);
explain query plan select distinct bill_state from customers;
0|0|TABLE customers
Since DISTINCT is similar to GROUP BY, you can rewrite you query
to pick up the index for each column:
explain query plan
SELECT bill_state as the_result from customers group by 1
UNION SELECT bill_state FROM invoice_master group by 1
UNION SELECT ship_state from customers group by 1
UNION SELECT ship_state from invoice_master group by 1;
0|0|TABLE customers WITH INDEX customers_bill_state ORDER BY
0|0|TABLE invoice_master WITH INDEX invoice_master_bill_state ORDER BY
0|0|TABLE customers WITH INDEX customers_ship_state ORDER BY
0|0|TABLE invoice_master WITH INDEX invoice_master_ship_state ORDER BY
____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
