[TYPO3-dev] Use of subqueries

[Bernhard Kraft]

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