Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN transaction tt ON i.prodcode = tt.prodcode AND tt.date = '2005-01-01'

Re: sub query is extermely slow

sam wun [EMAIL PROTECTED] wrote on 01/20/2005 11:45:40 AM: [EMAIL PROTECTED] wrote: SELECT DISTINCT i.basename FROM inventory i INNER JOIN transaction t ON i.prodcode = t.prodcode AND t.date = '2004-01-01' AND t.date = '2004-01-31' INNER JOIN

sub query is extermely slow

Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode = t.custcode and i.basename is not NULL and i.prodname is not NULL and

RE: sub query is extermely slow

Sam, Can you send the output of the following: #explain your query\G Clint From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I

Re: sub query is extermely slow

From: sam wun [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: sub query is extermely slow Date: Wed, 19 Jan 2005 20:02:37 +0800 Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t

Re: sub query is extermely slow

'%buffer%';: Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 20:39:41 +0800 Clint Edwards wrote: Sam, Can you send the output of the following: #explain your query\G Thanks

Re: sub query is extermely slow

] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information: When was the last time 'analyze table table_name' (inventory, transaction, customer

Re: sub query is extermely slow

index to a field after a table is created? Thanks Sam Clint From: sam wun [EMAIL PROTECTED] To: Clint Edwards [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: sub query is extermely slow Date: Wed, 19 Jan 2005 22:05:58 +0800 Clint Edwards wrote: Sam, Can you send the following information

RE: sub query is extermely slow

Check ALTER statement in MySQL doc. It explains how to add/modify an index after a table has been created. -Original Message- From: sam wun [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 19, 2005 10:00 AM Cc: mysql@lists.mysql.com Subject: Re: sub query is extermely slow

Re: sub query is extermely slow

sam wun [EMAIL PROTECTED] wrote on 01/19/2005 07:02:37 AM: Hi list, The following sql statement takes 3 mintues to complete the query. How can I improve its speed? select DISTINCT i.basename from inventory i, transaction t, customer c where i.prodcode = t.prodcode and c.custcode =

RE: sub query is extermely slow

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 19 January 2005 15:04 To: sam wun Cc: mysql@lists.mysql.com Subject: Re: sub query is extermely slow sam wun [EMAIL PROTECTED] wrote on 01/19/2005 07:02:37 AM: Hi list, The following sql statement

Re: sub query is extermely slow

[EMAIL PROTECTED] wrote: I would simplify it by converting everything to us explicit (not implicit) JOIN statements,skipping unnecessary type conversions, and logically merging your conditions. Here is your original query, slightly reformatted. SELECT DISTINCT i.basename FROM inventory i,