slow website

by themba, Saturday, February 16, 2019, 17:31 (1988 days ago) @ themba

I have downgraded to echo() profiling because non of the more advanced profilers really work on my LTS ubuntu (php 7.0 :s).

It seems that 11 seconds (99% of the request time) are spent in $tagsDao->getWithUnread();

Possibly the SQL does not get optimized. This is the query in question that takes almost the complete 11 seconds of the request:
SELECT tag, color, COUNT(items.id) AS unread
FROM tags AS tags,
sources AS sources
LEFT OUTER JOIN items AS items
ON (items.source=sources.id AND items.unread=1)
WHERE CONCAT(',', sources.tags, ',') LIKE CONCAT('%,', tags.tag, ',%') COLLATE utf8mb4_general_ci
GROUP BY tags.tag, tags.color
ORDER BY LOWER(tags.tag);

I'm wondering why selfoss uses this "WHERE ... CONCAT() LIKE CONCAT()" construction to join the tags to the sources rather than a tags-sources association table. The latter is a common structure in relational databases and probably gets optimized much better by the database system.