t3dev/tt_news — pull request #1 by energy-de (2014-02-17 18:06:36+00:00)
Title: optimized category queries

We often need to retrieve news items that are in more than one catalog. Unfortunately, as we have to deal with a large number of items (around 30000 news, 300 catalogs), tt_news slows down a lot.

The reason for this was tracked down to the code immediately below this comment:
```php
// if categoryMode is 'show items AND' it's required to check if the records in the result do actually have the same number of categories as in $this->catExclusive
```
To obtain the items which are in all categories, a "helper query" was used, obtaining all items in any of these categories. Then the number of categories were counted for each item and a new query was formed with a huge set containing only items that are members of all categories.

This patch replaces this code by a single, efficient query that yields the excact same result:
```sql
LEFT JOIN tt_news_cat_mm AS c0 ON c0.uid_local=tt_news.uid
...
LEFT JOIN tt_news_cat_mm AS cN ON cN.uid_local=tt_news.uid
WHERE (c0.uid_foreign=cat0) ... AND (cN.uid_foreign=catN)
```

Likewise, category mode -2 (NOT AND) was optimized using a query of the following format:
```sql
WHERE (NOT EXISTS
(SELECT 1 FROM tt_news_cat_mm c WHERE (c.uid_local = tt_news.uid) AND
(c.uid_foreign IN (cat0, ..., catN))))
```