Use of subqueries

[Rik Willems - Actiview]

Hi all,

For an extension I planning I wonder about the following. MySQL offers
the opportunity to use subqueries (and subsubqueries etc). Can any one
say anything about the use of subqueries in TYPO3 and how they influence
performance?

An alternative would be to first select through php what you want and
than afterward write a query without subqueries.

Looking forward to any ideas.

Greets, Rik
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Bernhard Kraft-2]

Rik Willems schrieb:

> For an extension I planning I wonder about the following. MySQL offers
> the opportunity to use subqueries (and subsubqueries etc). Can any one
> say anything about the use of subqueries in TYPO3 and how they influence
> performance?

TYPO3 does not impact on the performance of your queries in any way -
except that you use API methods for submitting the query and fetching
the results.


> An alternative would be to first select through php what you want and
> than afterward write a query without subqueries.

It depends on what you want to achieve. In many cases it is also
possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
Have a look at the mysql-manual for the syntax of those or any good
documentation and/or book about SQL and "JOIN" in general to get a
glimpse how those could help you.

If you want to retrieve all rows, and associated rows, referenced by a
database relation, in many cases JOINs are possible.

Those are even faster in most of the cases.

Of course you can also use subqueries, but be aware, that a query using
subqueries could take much longer than other solution.

Making a query in PHP/TYPO3 and then another one for each result row is
most probably the slowest solutions, but also possible under special
circumstances (detail/single view, etc.)


So it depends on your application. You can of course place a subquery in
the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API calls.


greets,
Bernhard
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Rik Willems - Actiview]

Hi Bernhard,

Thank you for your clear and extensive response.
Basically I want to create a categorization (also the extkey) extension.
I thought about a specific solution while working on a recent project.

There is a hook in enableFields that allows you to add to the where
statement. This is in my opinion the only way to create a global
categorization extension that doesn't require any extension to be
modified to work.

But, the enableFields function only allows you to add to the 'where'
part of a statement. But for a join to work you also require the 'from'
part. A subquery would prove a good solution in that case I believe.

In case this raises any new insights please let me know.

Greets, Rik

Bernhard Kraft schreef:

> Rik Willems schrieb:
>
>> For an extension I planning I wonder about the following. MySQL offers
>> the opportunity to use subqueries (and subsubqueries etc). Can any one
>> say anything about the use of subqueries in TYPO3 and how they influence
>> performance?
>
> TYPO3 does not impact on the performance of your queries in any way -
> except that you use API methods for submitting the query and fetching
> the results.
>
>
>> An alternative would be to first select through php what you want and
>> than afterward write a query without subqueries.
>
> It depends on what you want to achieve. In many cases it is also
> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
> Have a look at the mysql-manual for the syntax of those or any good
> documentation and/or book about SQL and "JOIN" in general to get a
> glimpse how those could help you.
>
> If you want to retrieve all rows, and associated rows, referenced by a
> database relation, in many cases JOINs are possible.
>
> Those are even faster in most of the cases.
>
> Of course you can also use subqueries, but be aware, that a query using
> subqueries could take much longer than other solution.
>
> Making a query in PHP/TYPO3 and then another one for each result row is
> most probably the slowest solutions, but also possible under special
> circumstances (detail/single view, etc.)
>
>
> So it depends on your application. You can of course place a subquery in
> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API calls.
>
>
> greets,
> Bernhard

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[ries van Twisk-2]

On Oct 12, 2009, at 5:41 AM, Bernhard Kraft wrote:

> Rik Willems schrieb:
>
>> For an extension I planning I wonder about the following. MySQL  
>> offers
>> the opportunity to use subqueries (and subsubqueries etc). Can any  
>> one
>> say anything about the use of subqueries in TYPO3 and how they  
>> influence
>> performance?
>
> TYPO3 does not impact on the performance of your queries in any way -
> except that you use API methods for submitting the query and fetching
> the results.
>
>
>> An alternative would be to first select through php what you want and
>> than afterward write a query without subqueries.
>
> It depends on what you want to achieve. In many cases it is also
> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
> Have a look at the mysql-manual for the syntax of those or any good
> documentation and/or book about SQL and "JOIN" in general to get a
> glimpse how those could help you.
>
> If you want to retrieve all rows, and associated rows, referenced by a
> database relation, in many cases JOINs are possible.
>
> Those are even faster in most of the cases.
>
> Of course you can also use subqueries, but be aware, that a query  
> using
> subqueries could take much longer than other solution.

OT, FYI:

With MySQL I found myself in a situation I needed to do this:

- SELECT * FROM tableA WHERE fieldB IN(SELECT fieldC FROM TABLEC GROUP  
BY fieldC);

This performance truly HORRIBLY in MySQL, this is because it get's  
rewritten to something in the line of :

- SELECT ... FROM tableA WHERE EXISTS (SELECT 1 FROM fieldB WHERE  
fieldB. fieldC = tableA. fieldB);

And that rewrite put's performance down the drain...

In this situation it's best to retrieve your dataset from tableB  
first, then add that as a comma separated list to you IN,
to prevent the query being re-written.


PS: I tested this with PostgreSQL, and for PG this isn't a problem....

Conclusion,, sub-queries are not always slow, MySQL makes them slow!



>
> Making a query in PHP/TYPO3 and then another one for each result row  
> is
> most probably the slowest solutions, but also possible under special
> circumstances (detail/single view, etc.)

See above, can actually by much faster...

>
>
> So it depends on your application. You can of course place a  
> subquery in
> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API  
> calls.
>
>
> greets,
> Bernhard
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev@...
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

                        regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries@...        web:   http://www.rvantwisk.nl/     
skype: callto://r.vantwisk
Phone: [phone]    Cell: [phone]                   SIP:  
[phone]








                        regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries@...        web:   http://www.rvantwisk.nl/     
skype: callto://r.vantwisk
Phone: [phone]    Cell: [phone]                   SIP:  
[phone]







_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Martin Kutschker-7]

Bernhard Kraft schrieb:
>
> So it depends on your application. You can of course place a subquery in
> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API calls.

AFAIK the query will then be incompatible with DBAL. :(

Masi
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Martin Kutschker-7]

Bernhard Kraft schrieb:
>
> It depends on what you want to achieve. In many cases it is also
> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".

And be sure to check the output of EXPLAIN for both queries. Depending on your query, your indices
and the number of rows in the tables one version may be faster. I don't think that sub-queries are
slower in all circumstances.

Masi
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Rik Willems - Actiview]

Hi Masi,

What exactly do you mean with EXPLAIN?

Greets, Rik


Martin Kutschker wrote:
> Bernhard Kraft schrieb:
>> It depends on what you want to achieve. In many cases it is also
>> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
>
> And be sure to check the output of EXPLAIN for both queries. Depending on your query, your indices
> and the number of rows in the tables one version may be faster. I don't think that sub-queries are
> slower in all circumstances.
>
> Masi
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Rik Willems - Actiview]

Hi Ries,

This is good to know. Thank you for the input.
Might it be that the 'group by' makes this happen? In the past I needed
to use MS Access and there the 'group by' does strange things. Perhaps
select distinct works better?

Greets, Rik


Ries van Twisk wrote:

>
> On Oct 12, 2009, at 5:41 AM, Bernhard Kraft wrote:
>
>> Rik Willems schrieb:
>>
>>> For an extension I planning I wonder about the following. MySQL offers
>>> the opportunity to use subqueries (and subsubqueries etc). Can any one
>>> say anything about the use of subqueries in TYPO3 and how they influence
>>> performance?
>>
>> TYPO3 does not impact on the performance of your queries in any way -
>> except that you use API methods for submitting the query and fetching
>> the results.
>>
>>
>>> An alternative would be to first select through php what you want and
>>> than afterward write a query without subqueries.
>>
>> It depends on what you want to achieve. In many cases it is also
>> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
>> Have a look at the mysql-manual for the syntax of those or any good
>> documentation and/or book about SQL and "JOIN" in general to get a
>> glimpse how those could help you.
>>
>> If you want to retrieve all rows, and associated rows, referenced by a
>> database relation, in many cases JOINs are possible.
>>
>> Those are even faster in most of the cases.
>>
>> Of course you can also use subqueries, but be aware, that a query using
>> subqueries could take much longer than other solution.
>
> OT, FYI:
>
> With MySQL I found myself in a situation I needed to do this:
>
> - SELECT * FROM tableA WHERE fieldB IN(SELECT fieldC FROM TABLEC GROUP
> BY fieldC);
>
> This performance truly HORRIBLY in MySQL, this is because it get's
> rewritten to something in the line of :
>
> - SELECT ... FROM tableA WHERE EXISTS (SELECT 1 FROM fieldB WHERE
> fieldB. fieldC = tableA. fieldB);
>
> And that rewrite put's performance down the drain...
>
> In this situation it's best to retrieve your dataset from tableB first,
> then add that as a comma separated list to you IN,
> to prevent the query being re-written.
>
>
> PS: I tested this with PostgreSQL, and for PG this isn't a problem....
>
> Conclusion,, sub-queries are not always slow, MySQL makes them slow!
>
>
>
>>
>> Making a query in PHP/TYPO3 and then another one for each result row is
>> most probably the slowest solutions, but also possible under special
>> circumstances (detail/single view, etc.)
>
> See above, can actually by much faster...
>
>>
>>
>> So it depends on your application. You can of course place a subquery in
>> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API
>> calls.
>>
>>
>> greets,
>> Bernhard
>> _______________________________________________
>> TYPO3-dev mailing list
>> TYPO3-dev@...
>> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>
>
>             regards, Ries van Twisk
>
> -------------------------------------------------------------------------------------------------
>
> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
> WebORB PostgreSQL DB-Architect
> email: ries@...        web:   http://www.rvantwisk.nl/    skype:
> callto://r.vantwisk
> Phone: [phone]    Cell: [phone]                   SIP:
> [phone]
>
>
>
>
>
>
>
>
>             regards, Ries van Twisk
>
> -------------------------------------------------------------------------------------------------
>
> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
> WebORB PostgreSQL DB-Architect
> email: ries@...        web:   http://www.rvantwisk.nl/    skype:
> callto://r.vantwisk
> Phone: [phone]    Cell: [phone]                   SIP:
> [phone]
>
>
>
>
>
>
>

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Rik Willems - Actiview]

Martin Kutschker wrote:
> Bernhard Kraft schrieb:
>> So it depends on your application. You can of course place a subquery in
>> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API calls.
>
> AFAIK the query will then be incompatible with DBAL. :(
>
> Masi

Hi Masi,

Thank wouldn't be a good thing of course. In that case the best solution
is to first select the right IDs and then insert those.
The biggest problem is that there is no way to edit the complete select
query, as far as I know.

Greets, Rik

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[ries van Twisk-2]

On Oct 12, 2009, at 11:20 AM, Rik Willems wrote:

> Hi Ries,
>
> This is good to know. Thank you for the input.
> Might it be that the 'group by' makes this happen? In the past I  
> needed
> to use MS Access and there the 'group by' does strange things. Perhaps
> select distinct works better?

The group by should make things better, but the query optimizer could  
add even because
it knows under what context the sub-query is used.

In my case i had to sub-select around a 1000 manufacturers out of a  
products list of 2 mil products.

the real problem is the re-write that MySQL does in this case.

Ries

>
> Greets, Rik
>
>
> Ries van Twisk wrote:
>>
>> On Oct 12, 2009, at 5:41 AM, Bernhard Kraft wrote:
>>
>>> Rik Willems schrieb:
>>>
>>>> For an extension I planning I wonder about the following. MySQL  
>>>> offers
>>>> the opportunity to use subqueries (and subsubqueries etc). Can  
>>>> any one
>>>> say anything about the use of subqueries in TYPO3 and how they  
>>>> influence
>>>> performance?
>>>
>>> TYPO3 does not impact on the performance of your queries in any  
>>> way -
>>> except that you use API methods for submitting the query and  
>>> fetching
>>> the results.
>>>
>>>
>>>> An alternative would be to first select through php what you want  
>>>> and
>>>> than afterward write a query without subqueries.
>>>
>>> It depends on what you want to achieve. In many cases it is also
>>> possible to avoid subqueries by clever use of "JOIN" or "LEFT JOIN".
>>> Have a look at the mysql-manual for the syntax of those or any good
>>> documentation and/or book about SQL and "JOIN" in general to get a
>>> glimpse how those could help you.
>>>
>>> If you want to retrieve all rows, and associated rows, referenced  
>>> by a
>>> database relation, in many cases JOINs are possible.
>>>
>>> Those are even faster in most of the cases.
>>>
>>> Of course you can also use subqueries, but be aware, that a query  
>>> using
>>> subqueries could take much longer than other solution.
>>
>> OT, FYI:
>>
>> With MySQL I found myself in a situation I needed to do this:
>>
>> - SELECT * FROM tableA WHERE fieldB IN(SELECT fieldC FROM TABLEC  
>> GROUP
>> BY fieldC);
>>
>> This performance truly HORRIBLY in MySQL, this is because it get's
>> rewritten to something in the line of :
>>
>> - SELECT ... FROM tableA WHERE EXISTS (SELECT 1 FROM fieldB WHERE
>> fieldB. fieldC = tableA. fieldB);
>>
>> And that rewrite put's performance down the drain...
>>
>> In this situation it's best to retrieve your dataset from tableB  
>> first,
>> then add that as a comma separated list to you IN,
>> to prevent the query being re-written.
>>
>>
>> PS: I tested this with PostgreSQL, and for PG this isn't a  
>> problem....
>>
>> Conclusion,, sub-queries are not always slow, MySQL makes them slow!
>>
>>
>>
>>>
>>> Making a query in PHP/TYPO3 and then another one for each result  
>>> row is
>>> most probably the slowest solutions, but also possible under special
>>> circumstances (detail/single view, etc.)
>>
>> See above, can actually by much faster...
>>
>>>
>>>
>>> So it depends on your application. You can of course place a  
>>> subquery in
>>> the WHERE part of TYPO3_DB->exec_SELECTquery and other TYPO3_DB API
>>> calls.
>>>
>>>
>>> greets,
>>> Bernhard
>>> _______________________________________________
>>> TYPO3-dev mailing list
>>> TYPO3-dev@...
>>> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev
>>
>>
>>            regards, Ries van Twisk
>>
>> -------------------------------------------------------------------------------------------------
>>
>> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
>> WebORB PostgreSQL DB-Architect
>> email: ries@...        web:   http://www.rvantwisk.nl/     
>> skype:
>> callto://r.vantwisk
>> Phone: [phone]    Cell: [phone]                   SIP:
>> [phone]
>>
>>
>>
>>
>>
>>
>>
>>
>>            regards, Ries van Twisk
>>
>> -------------------------------------------------------------------------------------------------
>>
>> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
>> WebORB PostgreSQL DB-Architect
>> email: ries@...        web:   http://www.rvantwisk.nl/     
>> skype:
>> callto://r.vantwisk
>> Phone: [phone]    Cell: [phone]                   SIP:
>> [phone]
>>
>>
>>
>>
>>
>>
>>
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev@...
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

                        regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries@...        web:   http://www.rvantwisk.nl/     
skype: callto://r.vantwisk
Phone: [phone]    Cell: [phone]                   SIP:  
[phone]







_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Martin Kutschker-7]

Rik Willems schrieb:
> Hi Masi,
>
> What exactly do you mean with EXPLAIN?

Mysql will tell you how it executes a query if you put an EXPLAIN before it.

Fire up phpMyAdmin and execute for example this statement:

EXPLAIN SELECT * FROM pages p LEFT JOIN pages_language_overlay plo ON (p.uid=plo.pid)

EXPLAIN helps a lot to check if your queries and indices are efficient! Mysql may surprise you how
it uses your indices and in what order it executes the joins.

See the Mysql docs for more information.

Masi
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Rik Willems - Actiview]

Hi Martin,

Thank you for this, it is interesting information.

Greets, Rik

Martin Kutschker schreef:

> Rik Willems schrieb:
>> Hi Masi,
>>
>> What exactly do you mean with EXPLAIN?
>
> Mysql will tell you how it executes a query if you put an EXPLAIN before it.
>
> Fire up phpMyAdmin and execute for example this statement:
>
> EXPLAIN SELECT * FROM pages p LEFT JOIN pages_language_overlay plo ON (p.uid=plo.pid)
>
> EXPLAIN helps a lot to check if your queries and indices are efficient! Mysql may surprise you how
> it uses your indices and in what order it executes the joins.
>
> See the Mysql docs for more information.
>
> Masi

_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Christian Kuhn-2]

Hey,

Martin Kutschker wrote:

>> What exactly do you mean with EXPLAIN?
>
> Mysql will tell you how it executes a query if you put an EXPLAIN before it.
>
> Fire up phpMyAdmin and execute for example this statement:
>
> EXPLAIN SELECT * FROM pages p LEFT JOIN pages_language_overlay plo ON (p.uid=plo.pid)
>
> EXPLAIN helps a lot to check if your queries and indices are efficient! Mysql may surprise you how
> it uses your indices and in what order it executes the joins.

+1. EXPLAIN is really usefull.

A bit more about mysql performance:

It's important to know which queries are slow or could be improved.
my.cnf:
# Log slow queries and log queries not using indexes
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Furthermore the FE admin panel is your friend. You could locate really
slow extensions and look for long running queries. Take a special look
to USER_INT queries. Always run queries on "real" data, if your tables
are not big enough mysql might decide to not use any index at all.

More reading.
mysqlperformanceblog.com is very good, here are some of the gems you
shouldn't miss:
http://www.mysqlperformanceblog.com/2009/09/19/multi-column-indexes-vs-index-merge/
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/


Greetings
Christian
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev

[Martin Kutschker-7]

Christian Kuhn schrieb:
>
> Furthermore the FE admin panel is your friend. You could locate really
> slow extensions and look for long running queries. Take a special look
> to USER_INT queries. Always run queries on "real" data, if your tables
> are not big enough mysql might decide to not use any index at all.

Right. And after doing a mass import/delete you could run ANALYZE TABLE.

Masi
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev@...
http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev