Support

donna.torr

Wednesday, 23 April 2025 15:05 EEST

Hey Guys, bit of a strange on a site using JFilters - the host has reported MySQL abuse - i.e. high resource usage and i think its related to JFilters. See below.

When reviewing the logs on the server we're seeing lots of queries such as this running:

SELECT `main`.`id` AS `value`,`main`.`title` AS `label`,`main`.`alias` AS `alias`,`main`.`metadesc` AS `metadescription`,`main`.`metakey` AS `metakeywords`,`main`.`parent_id` AS `parent_id`,count(DISTINCT(items.`id`)) AS `count`
FROM i1aen_tags AS main
LEFT JOIN `i1aen_tags` AS  tree ON (`main`.`lft` <= `tree`.`lft` AND
            `tree`. `lft` < `main`.`rgt`) OR
            (`tree`.`lft` < `main`.`lft` AND `main`.`rgt` < `tree`.`rgt`)
LEFT JOIN `i1aen_contentitem_tag_map` AS  itemValueRef ON main.id=itemValueRef.tag_id
LEFT JOIN `i1aen_content` AS  items ON itemValueRef.content_item_id=items.id
LEFT JOIN `i1aen_content` AS  filter1 ON itemValueRef.content_item_id=filter1.id
LEFT JOIN `i1aen_fields_values` AS  filter3 ON itemValueRef.content_item_id=filter3.item_id
LEFT JOIN `i1aen_fields_values` AS  filter6 ON itemValueRef.content_item_id=filter6.item_id
LEFT JOIN `i1aen_fields_values` AS  filter7 ON itemValueRef.content_item_id=filter7.item_id
LEFT JOIN `i1aen_fields_values` AS  filter8 ON itemValueRef.content_item_id=filter8.item_id
LEFT JOIN `i1aen_fields_values` AS  filter10 ON itemValueRef.content_item_id=filter10.item_id
WHERE (`main`.`published` = 1 AND `tree`.`id` = 1 AND `itemValueRef`.`type_id` = 1 AND `items`.`state` = '1' AND `items`.`access` IN (1,5) AND `filter3`.`field_id` = 15 AND `filter6`.`field_id` = 18 AND `filter7`.`field_id` = 19 AND `filter8`.`field_id` = 20 AND `filter10`.`field_id` = 23) AND (items.publish_up IS NULL OR `items`.`publish_up` <= '2025-04-23 10:03:00') AND (items.publish_down IS NULL OR `items`.`publish_down` >= '2025-04-23 10:03:00') AND (`filter1`.`catid` IN (21)) AND (`filter3`.`value` IN ('Venture Capital')) AND (`filter6`.`value` IN ('1 – 10 M')) AND (`filter7`.`value` IN ('Germany')) AND (`filter8`.`value` IN ('Funds')) AND (`filter10`.`value` IN ('Social Enterprise '))
GROUP BY main.`id`
ORDER BY `main`.`lft` ASC


Any suggestions?

sakinho

Wednesday, 23 April 2025 15:41 EEST

Hi

This is a query to generate the values of a tags filter.
It uses the values of other 5 filters to do that.

JFilters is not a light application, especially when you have many filters.
It can create quite heavy queries that scan a big part of your database.

There is no much you can do in the application level.
If you have a big website (with thousands of articles), make sure that you have a descent server, which is also configured properly.

Also make sure that, you are not getting DOS attacks that take advantage of the filters and their resouce intensiveness.

From our side, we are going to do some further analysis on the queries to optimize them to the best point and also developing some features to make them load faster.
The query you submitted will be used as well.

Kind regards,

Sakis Terzis

Founder and Lead Developer

System Task

Saturday, 24 May 2025 03:00 EEST

This ticket has been automatically closed.All tickets which have been inactive for a long time are automatically closed. If you believe that this ticket was closed in error, please contact us.

MySQL Abuse - large queries