[TYPO3-core] RFC #14818: Performance: Use mysql find_in_set-function in t3lib_db->listQuery()

[Xavier Perseguers]

Hijacking a bit this thread but as it is all about performance... 
Anyway, last post here.

> FYI, currently working on a PoC that parsing the queries twice may be
> avoided and would then speed up DBAL, at least with Oracle:
>
> http://bugs.typo3.org/view.php?id=15160

I attached patch is a proof-of-concept that it speeds up queries. The 
unit test makes sure both "normal" and "optimized" queries are still the 
same and outputs a debug message to show the performance gain:

Optimized run in 60.07950028393% of time of Normal for 500 loops

That's quite a huge gain of performance to be expected for TYPO3 in 
general...

The query that was tested is:

SELECT foo.uid
FROM tt_news AS foo
INNER JOIN tt_news_cat_mm ON tt_news_cat_mm.uid_local = foo.uid
WHERE tt_news_cat_mm.uid_foreign IN (
	SELECT foo.uid FROM tt_news_cat AS foo WHERE foo.hidden = 0
)
ORDER BY foo.uid

with remapping configuration:

$TYPO3_CONF_VARS['EXTCONF']['dbal']['mapping'] = array(
	'tt_news' => array(
		'mapTableName' => 'ext_tt_news',
		'mapFieldNames' => array(
			'uid'      => 'news_uid',
			'fe_group' => 'usergroup',
		),
	),
	'tt_news_cat' => array(
		'mapTableName' => 'ext_tt_news_cat',
		'mapFieldNames' => array(
			'uid' => 'cat_uid',
		),
	),
	'tt_news_cat_mm' => array(
		'mapTableName' => 'ext_tt_news_cat_mm',
		'mapFieldNames' => array(
			'uid_local' => 'local_uid',
		),
	),
);

which results in following query being actually sent to Oracle:

SELECT "foo"."news_uid" FROM "ext_tt_news" AS "foo" INNER JOIN 
"ext_tt_news_cat_mm" ON 
"ext_tt_news_cat_mm"."local_uid"="foo"."news_uid" WHERE 
"ext_tt_news_cat_mm"."uid_foreign" IN (SELECT "foo"."cat_uid" FROM 
"ext_tt_news_cat" AS "foo" WHERE "foo"."hidden" = 0) ORDER BY 
"foo"."news_uid"

Xavier