very slow query

[boomsya]

hi
thanks for tricks and speed ups

i have found very heavy query:

administrator/components/com_zoo/tables/category.php 
public function getAll($application_id, $published = false, $item_count = false, $user = null)

i think GROUP_CONCAT load CPU 100% because i have 50K items at all
and column GROUP_CONCAT(DISTINCT ci.item_id) as item_ids is very big

disabling counting items make site work as lightning but pagination disappearing at this hack

[boomsya]

SELECT c.*, GROUP_CONCAT(DISTINCT ci.item_id) as item_ids FROM #__zoo_category AS c USE INDEX (APPLICATIONID_ID_INDEX) LEFT JOIN #__zoo_category_item AS ci ON ci.category_id = c.id LEFT JOIN #__zoo_item AS i USE INDEX (MULTI_INDEX2) ON ci.item_id = i.id AND i.state = 1 WHERE c.application_id = 2 AND c.published = 1

limit 2 = 0.2657 sec
limit 20 = 0.3786 sec
limit 100 = 0.3843 sec

[boomsya]

without hacks:
SELECT c.*, GROUP_CONCAT(DISTINCT ci.item_id) as item_ids FROM ukr2y_zoo_category AS c USE INDEX (APPLICATIONID_ID_INDEX) LEFT JOIN ukr2y_zoo_category_item AS ci ON ci.category_id = c.id LEFT JOIN ukr2y_zoo_item AS i USE INDEX (MULTI_INDEX2) ON ci.item_id = i.id AND i.access IN(1, 5) AND i.state = 1 AND( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2018-10-10 18:05:24') AND(i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2018-10-10 18:05:24')WHERE c.application_id = 2 AND c.published = 1 limit 200

limit 20 = 0.5215 sec
limit 200 = 0.4556 sec

[boomsya]

when i replace GROUP_CONCAT(DISTINCT ci.item_id) as item_ids to "" as item_ids all fine
and pagination not disappear because public function getItems($published = false, $user = null, $orderby = '') check if items empty - then they get it in $this->app->table->item->getByCategory

[CB9TOIIIA]

when i replace GROUP_CONCAT(DISTINCT ci.item_id) as item_ids to "" as item_ids all fine
and pagination not disappear because public function getItems($published = false, $user = null, $orderby = '') check if items empty - then they get it in $this->app->table->item->getByCategory

Thank you for your interest. Do you have a better solution?

[boomsya]

just replace GROUP_CONCAT(DISTINCT ci.item_id) as item_ids to "" as item_ids
visually no differences on site