Re: [PHP-DB] query optimization
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 need some help re-writing it with EXISTS... Thanks for the help so far, You haven't said which parts of the query is slow. Try: Just the subselect query. If that's slow, try just the first part of the union. If that's not slow, try the second part. At least you'll know where to concentrate. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization - DB
MySQL < 5.0 can only use 1 index per table. MySQL >= 5.0 can use more than one via an index merge. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html On Sat, Sep 27, 2008 at 2:47 AM, Micah Gersten <[EMAIL PROTECTED]> wrote: > MySQL queries use 1 index per table, so to speed the query, we need to > know what indices you have for the 2 tables. > > Thank you, > Micah Gersten > onShore Networks > Internal Developer > http://www.onshore.com > > > > Yves Sucaet wrote: > > 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 > > > > > >> 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 blockunit bu inner join interactionparts ip on > >>> (bu.blockid = ip.part) > >>> where ip.blockid in > >>> > >>> > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > >>> > >>> > >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > >>> union > >>> select bu.blockid from blockunit bu > >>> where bu.blockid in > >>> > >>> > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > >>> > >>> > >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > >>> ) > >>> > >>> Thanks in advance, > >>> > >>> Yves > >>> > >>> > >>> > >>> > >> > > > > > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP-DB] query optimization - DB
Hi Micah, I'm learning here. Great! :-) How can I look this up? I'm pretty sure multiple fields are indexed. So should I specify explicitely which indices should be used? What fields do you think should be indexed? I do have control over the database and can create additional indices. 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 index per table, so to speed the query, we need to know what indices you have for the 2 tables. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Yves Sucaet wrote: 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 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 blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Thanks in advance, Yves -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization - DB
MySQL queries use 1 index per table, so to speed the query, we need to know what indices you have for the 2 tables. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Yves Sucaet wrote: > 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 > > >> 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 blockunit bu inner join interactionparts ip on >>> (bu.blockid = ip.part) >>> where ip.blockid in >>> >>> (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, >>> >>> >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) >>> union >>> select bu.blockid from blockunit bu >>> where bu.blockid in >>> >>> (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, >>> >>> >>> 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) >>> ) >>> >>> Thanks in advance, >>> >>> Yves >>> >>> >>> >>> >> > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization - DB
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 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 blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Thanks in advance, Yves -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
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 need some 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 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]> 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.blockid in (...) or bu.blockid in (...) however that'll probably be slower, but def. worth a try. -- Postgresql & php tutorials http://www.designmagick.com/ -- J.A. van Zanen
Re: [PHP-DB] query optimization
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 blockunit bu inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
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]> > 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.blockid in (...) > or > bu.blockid in (...) > > however that'll probably be slower, but def. worth a try. > > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > -- J.A. van Zanen
Re: [PHP-DB] query optimization
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.blockid in (...) or bu.blockid in (...) however that'll probably be slower, but def. worth a try. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
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 back from the main query What is the current execution plan? Jack 2008/9/26 YVES SUCAET <[EMAIL PROTECTED]> > 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 inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- J.A. van Zanen
Re: [PHP-DB] query optimization
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 from blockunit bu inner join interactionparts ip on (bu.blockid = ip.part) where ip.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) union select bu.blockid from blockunit bu where bu.blockid in (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) ) Which parts are slow? Run the inner query by itself to see if that's slow. If it is, take the first part of the union and run that. Is that slow? Same for the second. Also since you're doing a DISTINCT in the outer query, you can change the subquery to do a UNION ALL. A UNION will remove duplicates from the result sets, a UNION ALL will not. Since you're doing a distinct on the whole thing anyway, remove the duplicate check from the subquery - it'll make it slightly faster. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] query optimization
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 inner join interactionparts ip on > (bu.blockid = ip.part) > where ip.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > union > select bu.blockid from blockunit bu > where bu.blockid in > > (110936,110937,111641,111642,113140,113141,114925,114926,121161,121162,124087, > 124088,124562,124563,133358,133359,133409,133410,135304,135305,136096) > ) > > Thanks in advance, > > Yves > > > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
