[TYPO3-english] Slow Queries From TT-News

Xavier Perseguers xavier at typo3.org
Mon Nov 14 20:30:10 CET 2011


Georg Ringer wrote:
> Am 14.11.2011 19:58, schrieb Xavier Perseguers:
>> The big problem comes from having to deal with comma-separated values to
>> reference records (here fe_groups) instead of real foreign keys.
> so how should an ideal query look like instead?

 > (tt_news.fe_group='' OR tt_news.fe_group
 > IS NULL OR tt_news.fe_group='0'

No group

 > OR (tt_news.fe_group LIKE '%,0,%' OR
 > tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR
 > tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR
 > tt_news.fe_group LIKE '-1,%' OR tt_news.fe_group LIKE '%,-1' OR
 > tt_news.fe_group='-1')

-1 is a virtual group used to "Hide content at any login" [1]

So basically we search news which are targeted at no, group, group "0" 
(= no group), or that should be hidden at login.

With a proper structure, we would have something like

- table tt_news (uid, ...)
- table fe_groups (uid, ...)
- table tt_news_fegroups_mm (uid_local, uid_foreign)

uid_local is a foreign key to tt_news(uid)
uid_foreign is a foreign key to fe_groups(uid)

Thus search for news without any group:

SELECT * FROM tt_news WHERE NOT EXISTS(SELECT * FROM tt_news_fegroups_mm 
WHERE uid_local=tt_news.uid)

Search news with the group "-1":

SELECT * FROM tt_news WHERE EXISTS(SELECT * FROM tt_nerws_fegroups_mm 
WHERE uid_local=tt_news.uid AND uid_foreign=-1)

OK, when using DB constraints on foreign keys, we would need to have a 
real fe_group entry for -1 and -2 (or 0) but I guess you get the point.

The latter query could be rewritten as INNER JOIN without subquery as well.



Xavier Perseguers
Release Manager TYPO3 4.6

TYPO3 .... inspiring people to share!
Get involved: http://typo3.org

More information about the TYPO3-english mailing list