[TYPO3-english] Slow Queries From TT-News

[Xavier Perseguers]

Hi,

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.

HTH

[1] 
http://xavier.perseguers.ch/en/tutorials/typo3/articles/indexed-search-crawler.html#c868

-- 
Xavier Perseguers
Release Manager TYPO3 4.6

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