Skip to main content

Hello !Friendica Support
can one of the devs explain what this query is doing, please?

ELECT `item`.`parent-uri` AS `uri`, 0 AS `item_id`, `item`.`received` AS `order_date`, `author`.`url` AS `author-link` FROM `item` STRAIGHT_JOIN (SELECT `oid` FROM `term` WHERE `term` IN (SELECT SUBSTR(`term`, 2) FROM `search` WHERE `uid` = XXXX AND `term` LIKE '#%') AND `otype` = 1 AND `type` = 1 AND `uid` = 0) AS `term` ON `item`.`id` = `term`.`oid` STRAIGHT_JOIN `contact` AS `author` ON `author`.`id` = `item`.`author-id` WHERE `item`.`uid` = 0 AND `item`.`received` < '2019-08-05 17:51:43' AND `item`.`received` > '2019-07-05 10:48:14' AND `item`.`gravity` = 0 AND NOT `author`.`hidden` AND NOT `author`.`blocked`;

Thing is, that query is executed one time per minute for hours now. Always with the same UID. Each query takes 6-7 seconds, examining 6 million rows, returning 1115 results (rows sent).

Thank you.
This entry was edited (2 months ago)

Have a look at your webserver's log. I guess that some external service is crawling through your system.

I'll check. But the UID in that query belongs to an existing user. And the search function is configured as local search only. So, crawlers get a 403 when accessing /search.

Also, I wonder about that part...

FROM `search` WHERE `uid` = XXXX AND `term` LIKE '#%'

It lists all the hashtag searches by a specific user.


SELECT * FROM `search` WHERE `uid` = XXXX AND `term` LIKE '#%';

...returns only 10 rows for the UID.

But then it compares this list of 10 terms with the whole list of public hashtag terms SELECT `oid` FROM `term` WHERE `term` IN (SELECT SUBSTR(`term`, 2) FROM `search` WHERE `uid` = XXXX AND `term` LIKE '#%') AND `otype` = 1 AND `type` = 1 AND `uid` = 0

And then it uses the resulting terms to match items having these hashtags.

Yeah, ok. I did the complete query for my own UID and I got over 3600 rows as result. But where is this query for all items from all your hashtags for a given period of time used? I don't know a page or a search for this...

Ah, now I understand. That user has subscribed to all these hashtags. And the user has a running browser pointing to the /network page. And the user has got a 60 second page refresh.

Aha! looks like he has finished his work for today. The slow queries log doesn't show any new entry in the last 30 minutes. :-)

Aaaah, yes, the "saved searches".

OK. But I'm following more tags (15 saved searches) than the other user (10). And when I refresh my network page I don't get a slow query. There is still a mystery...

I guess the problem is the kind of tags the user is following.

I cannot tell which tags he is following. But I checked it. My tags are much more generic like #art or #science while his tags are more specific but nothing fancy.

Would long tags maybe slow the query down?

Can you run an EXPLAIN command on it? Maybe you can add a few indexes to the tables to make it run faster...
This entry was edited (2 months ago)