[TYPO3-dev] Use of subqueries

[Ries van Twisk]

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