query optimization

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 =3D ip.part) =

where ip.blockid in =

=

(110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,12=
4087,
124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
union =

select bu.blockid from blockunit bu =

where bu.blockid in =

=

(110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,12=
4087,
124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
)

Thanks in advance,

Yves



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> union
> select bu.blockid from blockunit bu
> where bu.blockid in
>
> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> )
>
> Thanks in advance,
>
> Yves
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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,121 161,121162,124087,
>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>> union
>> select bu.blockid from blockunit bu
>> where bu.blockid in
>>
>> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,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: query optimization

------=_Part_11920_2650918.1222387294090
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

> 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,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> union
> select bu.blockid from blockunit bu
> where bu.blockid in
>
>
> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,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

------=_Part_11920_2650918.1222387294090--

Re: 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: query optimization

------=_Part_12542_20128959.1222390194164
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

> 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

------=_Part_12542_20128959.1222390194164--

Re: 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,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> union
> select bu.blockid from blockunit bu
> where bu.blockid in
>
> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> )
>
> Thanks in advance,
>
> Yves
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: query optimization

------=_NextPart_000_0041_01C91FB9.354DC5F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 -----=20
From: Jack van Zanen=20
To: Chris=20
Cc: YVES SUCAET ; php-db@lists.php.net=20
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

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.=20


--=20
Postgresql & php tutorials
http://www.designmagick.com/





--=20
J.A. van Zanen

------=_NextPart_000_0041_01C91FB9.354DC5F0--

Re: query optimization - DB

Oh, sorry I forgot to mention this. It's a MySQL database.

----- Original Message -----
From: "Micah Gersten"
To: "YVES SUCAET"
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,121 161,121162,124087,
>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>> union
>> select bu.blockid from blockunit bu
>> where bu.blockid in
>>
>> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>> )
>>
>> Thanks in advance,
>>
>> Yves
>>
>>
>>
>>
>



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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"
> To: "YVES SUCAET"
> 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,121 161,121162,124087,
>>>
>>>
>>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>>> union
>>> select bu.blockid from blockunit bu
>>> where bu.blockid in
>>>
>>> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
>>>
>>>
>>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>>> )
>>>
>>> Thanks in advance,
>>>
>>> Yves
>>>
>>>
>>>
>>>
>>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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"
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"
>> To: "YVES SUCAET"
>> 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,121 161,121162,124087,
>>>>
>>>>
>>>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
>>>> union
>>>> select bu.blockid from blockunit bu
>>>> where bu.blockid in
>>>>
>>>> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
>>>>
>>>>
>>>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,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: query optimization - DB

------=_Part_26969_17519077.1222487207806
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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-optimizat ion.html

On Sat, Sep 27, 2008 at 2:47 AM, Micah Gersten 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"
> > To: "YVES SUCAET"
> > 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,121 161,121162,124087,
> >>>
> >>>
> >>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> >>> union
> >>> select bu.blockid from blockunit bu
> >>> where bu.blockid in
> >>>
> >>>
> (110936,110937,111641,111642,113140,113141,114925,114926,121 161,121162,124087,
> >>>
> >>>
> >>> 124088,124562,124563,133358,133359,133409,133410,135304,1353 05,136096)
> >>> )
> >>>
> >>> Thanks in advance,
> >>>
> >>> Yves
> >>>
> >>>
> >>>
> >>>
> >>
> >
> >
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_26969_17519077.1222487207806--

Re: 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

Fwd: query optimization

------=_Part_59100_4806333.1222647395694
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Sun, Sep 28, 2008 at 6:28 PM, Chris wrote:

> 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
>
>
If possible, see if you can have the sort space on the db table made bigger.
You could gain a lot of performance if you can adjust some the of settings
in the mysql control file.


sorry, hit reply instead of reply all (my bad)

--

Bastien

Cat, the other other white meat



--

Bastien

Cat, the other other white meat

------=_Part_59100_4806333.1222647395694--

Foreign Key Versus Table Index

Hi,

I'm slightly confused about foriegn keys and indexes on mysql innodb tables.
Foreign key constraints create a reference between two tables and
indexes make queries on a particular table faster if the index is on a
field in the where or order by clause.

My question was whether say for the following two tables:

Person Car

Id Id
Name PersonId
Address Make
Phone Number Colour

If I create a foriegn key linking the id field in person and the
personid field in car, do I need to create another index in car table
specifically for the personid field if I was running a query such as:

"SELECT Id FROM car WHERE personid={personkeynumber}"?

Thanks for your help.

Jemma

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Foreign Key Versus Table Index

------=_Part_97815_6821146.1222944617542
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I am not up to scratch with innodb, but in oracle
you would have a Primary key on both the id fields in the Car and person
table and a Foreign key on PersonId linking it to Id in the Person table.

IN your select an index on PersonId would be beneficial if the tables get
large.

Jack

2008/10/2 J Hussein

> Hi,
>
> I'm slightly confused about foriegn keys and indexes on mysql innodb
> tables.
> Foreign key constraints create a reference between two tables and indexes
> make queries on a particular table faster if the index is on a field in the
> where or order by clause.
>
> My question was whether say for the following two tables:
>
> Person Car
>
> Id Id
> Name PersonId
> Address Make
> Phone Number Colour
>
> If I create a foriegn key linking the id field in person and the personid
> field in car, do I need to create another index in car table specifically
> for the personid field if I was running a query such as:
>
> "SELECT Id FROM car WHERE personid={personkeynumber}"?
>
> Thanks for your help.
>
> Jemma
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>


--
J.A. van Zanen

------=_Part_97815_6821146.1222944617542--

Re: Foreign Key Versus Table Index

------=_Part_16998_31782020.1222985970174
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On Thu, Oct 2, 2008 at 6:50 AM, Jack van Zanen wrote:

> I am not up to scratch with innodb, but in oracle
> you would have a Primary key on both the id fields in the Car and person
> table and a Foreign key on PersonId linking it to Id in the Person table.
>
> IN your select an index on PersonId would be beneficial if the tables get
> large.
>
> Jack
>
> 2008/10/2 J Hussein
>
> > Hi,
> >
> > I'm slightly confused about foriegn keys and indexes on mysql innodb
> > tables.
> > Foreign key constraints create a reference between two tables and indexes
> > make queries on a particular table faster if the index is on a field in
> the
> > where or order by clause.
> >
> > My question was whether say for the following two tables:
> >
> > Person Car
> >
> > Id Id
> > Name PersonId
> > Address Make
> > Phone Number Colour
> >
> > If I create a foriegn key linking the id field in person and the personid
> > field in car, do I need to create another index in car table
> specifically
> > for the personid field if I was running a query such as:
> >
> > "SELECT Id FROM car WHERE personid={personkeynumber}"?
> >
> > Thanks for your help.
> >
> > Jemma
> >
> > --
> > PHP Database Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php
> >
> >
>
>
> --
> J.A. van Zanen
>

Jenna,

FKs are not to create relationships but act as constraints inside the data
base. What that means is that for a record to be created that has a FK in
another table, that FK'ed record MUST exist in the table before you can add
that record.

Ex. AN Order table and a Customer table

If the Order table references the Customer table as a FK on the CustomerID
field, the CustomerId record MUST exist in the table before the order table
can be filled.

It does not alleviate the need to have a primary key on the other table.

http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-co nstraints.htmlfor
more details

--

Bastien

Cat, the other other white meat

------=_Part_16998_31782020.1222985970174--