[TYPO3-dev] Use of subqueries

[Ries van Twisk]

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 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
>>
>>
>>
>>
>>
>>
>>
> _______________________________________________
> 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