>>> Try - and I mean *try* :-) >>> >>> SELECT Customers.* FROM Customers, Subscriptions >>> LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) >>> LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && >>> (Ordered_Items.productid = '23')) >>> WHERE Customers.id=Subscriptions.customerid >>> AND Subscriptions.listid='2' >>> AND Ordered_Items.orderid IS NULL >>> AND Orders.customerid IS NULL >>> AND NOT Customers.bad_email; >> >> Well, MySQL has been chugging on this one for about 15 minutes now, and >> it's >> still going. So I'm not sure if it is a problem with the query or the >> optimization of my MySQL databases. There are 30,000+ records in the >> Customers database, but the query should have finished by now, right? >> > Well, my best guess is that you should consider indexing your tables... > If you haven't done so already, indexing will cause dramatic effect on > the speed of the queries... > > Peter Normann
Okay. I've indexed all fields involved, and I've gotten the query time down to under 3 seconds! However, I've discovered a flaw in the query. A customer can order multiple times. The above query still includes customers that have ordered product #23, but have also ordered other products on separate orders. Example: Customers: Subscriptions: +------+-----------+ +------------+--------+ | id | bad_email | | customerid | listid | +------+-----------+ +------------+--------+ | 1001 | 0 | | 1001 | 2 | +------+-----------+ +------------+--------+ Orders: Ordered_Items: +----+------------+ +---------+-----------+ | id | customerid | | orderid | productid | +----+------------+ +---------+-----------+ | 1 | 1001 | | 1 | 23 | | 2 | 1001 | | 2 | 98 | +----+------------+ +---------+-----------+ Customer #1001 would be included in the found set of the query above. He would be removed because he ordered product #23 on order #1, but he would also be included because he didn't order product #23 on order #2. Any ideas on how to exclude these multi-order customers? Jamie Jamie Tibbetts ..... [EMAIL PROTECTED] Epigroove ..... http://www.epigroove.com/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
