Re: [PHP-DB] query optimization

2008-09-28 Thread Chris
Yves Sucaet wrote: Hi Jack, I'm expecting less than 10 records in the resulting set. The BlockUnit table contains 337,253 records; the InteractionParts table contains 279,953 records. It takes currently 8.3 seconds to execute the query as I have it. Erh, this is embarassing but I'm going to n

Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Glen Synergy
a MySQL database. > > > > - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> > > To: "YVES SUCAET" <[EMAIL PROTECTED]> > > Cc: > > Sent: Thursday, September 25, 2008 7:55 PM > > Subject: Re: [PHP-DB] query

Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Yves Sucaet
ces. Can you help out rewriting the query using EXISTS syntax? Thanks in advance, Yves - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> To: Sent: Friday, September 26, 2008 11:47 AM Subject: Re: [PHP-DB] query optimization - DB MySQL queries use 1 in

Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Micah Gersten
database. > > - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> > To: "YVES SUCAET" <[EMAIL PROTECTED]> > Cc: > Sent: Thursday, September 25, 2008 7:55 PM > Subject: Re: [PHP-DB] query optimization > > >> Other question is, what

Re: [PHP-DB] query optimization - DB

2008-09-26 Thread Yves Sucaet
Oh, sorry I forgot to mention this. It's a MySQL database. - Original Message - From: "Micah Gersten" <[EMAIL PROTECTED]> To: "YVES SUCAET" <[EMAIL PROTECTED]> Cc: Sent: Thursday, September 25, 2008 7:55 PM Subject: Re: [PHP-DB] query optimization

Re: [PHP-DB] query optimization

2008-09-26 Thread Yves Sucaet
me help re-writing it with EXISTS... Thanks for the help so far, Yves - Original Message - From: Jack van Zanen To: Chris Cc: YVES SUCAET ; php-db@lists.php.net Sent: Thursday, September 25, 2008 7:49 PM Subject: Re: [PHP-DB] query optimization If you can answer the other

Re: [PHP-DB] query optimization

2008-09-25 Thread Micah Gersten
Other question is, what DB is this for? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: > How could I rewrite the following query so it runs faster: > > select distinct location from blockunit where blockid in ( > select bu.blockid from b

Re: [PHP-DB] query optimization

2008-09-25 Thread Jack van Zanen
If you can answer the other questions that would help as well you can try rewriting using "exist" instead of "in" But without the basic information like number of records expected and explain plan it is very hard to come up with a better solution. Brgds Jack 2008/9/26 Chris <[EMAIL PROTECTED

Re: [PHP-DB] query optimization

2008-09-25 Thread Chris
Jack van Zanen wrote: Hi If I am not mistaken, the second part of the union contains all rows that are in the first part of the union. just remove the first part. Kind of. The first part is a join, the second isn't. I was going to suggest rewriting the subquery into a single: where ip.block

Re: [PHP-DB] query optimization

2008-09-25 Thread Jack van Zanen
Hi If I am not mistaken, the second part of the union contains all rows that are in the first part of the union. just remove the first part. Also What is the table sizes of the tables? How many records are expected to come back from the union sub query? How many records are expected to come

Re: [PHP-DB] query optimization

2008-09-25 Thread Chris
Micah Gersten wrote: What indices do you have? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: How could I rewrite the following query so it runs faster: select distinct location from blockunit where blockid in ( select bu.blockid fro

Re: [PHP-DB] query optimization

2008-09-25 Thread Micah Gersten
What indices do you have? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com YVES SUCAET wrote: > How could I rewrite the following query so it runs faster: > > select distinct location from blockunit where blockid in ( > select bu.blockid from blockunit bu in