Mitchell Vincent wrote: > SELECT customer_id FROM customers WHERE cust_balance != (select > coalesce(sum(balance_due), 0) FROM invoice WHERE status='Active' AND > invoice.customer_id = customers.customer_id) > > The above query is used to determine if any stored balances are out of > date. It works very well but is *really* slow when the customer and > invoice tables get into the thousands of rows. Is there a better way > to accomplish the same thing, or some combination of indexes I can > create to help speed that query up? Currently indexes are on the > customer_id columns of both tables as well as the cust_balance field > in customers. >
The index on cust_balance does no good for this query, sqlite must do a complete table scan of the customer table anyway. You could speed up the sub-select somewhat by replacing the index on invoice.customer_id with a compound index on invoice.customer_id and invoice.status. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
