Re: [sqlite] DISTINCT and Indexes
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] -
Re: [sqlite] DISTINCT and Indexes
Hey Andrew! Yes, though that's not what I'm trying to accomplish. The software allows entry of *any* text in the field and I want anything previously entered to appear in the lists after that -- that's why all the trouble. Thanks! On 7/8/07, Andrew Finkenstadt <[EMAIL PROTECTED]> wrote: On 7/7/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > > I have a query to get all states that a user might have entered, it is : > > 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? I'd > sure appreciate it! Thanks! > On the other hand, you could merely enter the Fifty States (and provinces, protectorates, commonwealths, etc) into a lookup table, and select from that much faster. :) --andy -- - 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] -
Re: [sqlite] DISTINCT and Indexes
On 7/7/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: I have a query to get all states that a user might have entered, it is : 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? I'd sure appreciate it! Thanks! On the other hand, you could merely enter the Fifty States (and provinces, protectorates, commonwealths, etc) into a lookup table, and select from that much faster. :) --andy
Re: [sqlite] DISTINCT and Indexes
> 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] -
[sqlite] DISTINCT and Indexes
I have a query to get all states that a user might have entered, it is : 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? I'd sure appreciate it! Thanks! -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
