Brake on the use of filters

Miken
Topic Author
Offline
Premium Member

11 years 7 months ago #35481 by Miken

Replied by Miken on topic Brake on the use of filters

search problems led to sql queries. That form filters.

Code:

SELECT fi.value AS value, fi.value AS text, COUNT(DISTINCT i.id) as found 
  FROM j25_content AS i JOIN j25_flexicontent_fields_item_relations AS fi 
  ON i.id = fi.item_id 
  LEFT JOIN j25_flexicontent_items_ext AS ie 
  ON ie.item_id = i.id 
  LEFT JOIN j25_flexicontent_types AS ty 
  ON ie.type_id = ty.id 
  LEFT JOIN j25_flexicontent_cats_item_relations AS rel 
  ON rel.itemid = i.id 
  LEFT JOIN j25_categories AS c 
  ON c.id = i.catid 
  LEFT JOIN j25_users AS u 
  ON u.id = i.created_by 
  WHERE 1 
  AND rel.catid IN ('22') 
  AND ( i.state IN (1, -5) OR i.created_by = 0 ) 
  AND ( ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= UTC_TIMESTAMP() ) OR i.created_by = 0 ) 
  AND ( ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= UTC_TIMESTAMP() ) OR i.created_by = 0 ) 
  AND i.id IN (SELECT id 
  FROM j25_content c JOIN j25_flexicontent_fields_item_relations rel 
  ON rel.item_id=c.id 
  AND rel.field_id = 19 
  WHERE 1 
  AND (value='20' ) 
  GROUP BY c.id) 
  AND fi.field_id =15 
  GROUP BY value

Each query is about 2 seconds. And as much as they added to the filter category. in my case, these requests 5 - 6. What to do about it is not yet known.

Flexicontent 3.1.1 & Joomla 3.7.1

Please Log in or Create an account to join the conversation.

ggppdk
Offline
Administrator

11 years 7 months ago #35483 by ggppdk

Replied by ggppdk on topic Brake on the use of filters

Hello,

Yes yes i know that these are the queries,

i know exactly , because i wrote them a few weeks ago

,
just last couple of days did not have time to test, and i needed a big test site , i have one with 12000 items, and if needed i will also add massively some custom field values with a small custom PHP script that generates random values

your feedback is always helpful, because users will always tell you something more than you already know or something you did not notice

now , i have setup the test site, i hope i will test fix the filters performance tomorrow or the day after or remove the code that causes the problem

again thanks for feedback

-- Flexicontent is Free but involves a big effort on our part.
Like the our support? (for a bug-free FC, despite having a long list of functions) Like the features? Like the ongoing development and future commitment to FLEXIcontent?
-- Add your voice to the FLEXIcontent JED listing with a 5-star...

Please Log in or Create an account to join the conversation.