[TYPO3-dev] Use of subqueries

[Rik Willems]

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 at lists.netfielders.de
>> 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 at vantwisk.nl        web:   http://www.rvantwisk.nl/    skype: 
> callto://r.vantwisk
> Phone: +1-810-476-4196    Cell: +593 9901 7694                   SIP: 
> +1-747-690-5133
> 
> 
> 
> 
> 
> 
> 
> 
>             regards, Ries van Twisk
> 
> ------------------------------------------------------------------------------------------------- 
> 
> tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS 
> WebORB PostgreSQL DB-Architect
> email: ries at vantwisk.nl        web:   http://www.rvantwisk.nl/    skype: 
> callto://r.vantwisk
> Phone: +1-810-476-4196    Cell: +593 9901 7694                   SIP: 
> +1-747-690-5133
> 
> 
> 
> 
> 
> 
>