Here is the dump of the categories table - http://www.filedropper.com/joscategories

Please replace the file content with your table prefix. I have used jos

This is the form field 'categoryedit'
administrator/components/com_categories/models/fields/categoryedit.php

• that is used to create the category parent form field (parent_id)

Looking at the PHP code the query has a sub-query (used in FROM of main query) and a left join with the table itself

• at least the left-join is not needed as it does nothing in any ? possible configuration for the field (and someone correct as i maybe wrong) also the subquery can be converted but it causes no performance problems as it has no joins in any configuration
• the performance issue must be from this left join but i have not tested, and the left join can be removed

https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_categories/models/fields/categoryedit.php#L195-L196

$query->from('(' . $subQuery->__toString() . ') AS a')
    ->join('LEFT', $db->quoteName('#__categories') . ' AS b ON a.lft > b.lft AND a.rgt < b.rgt');

can become:

$query->from('(' . $subQuery->__toString() . ') AS a')
    ;//->join('LEFT', $db->quoteName('#__categories') . ' AS b ON a.lft > b.lft AND a.rgt < b.rgt');

This is amazing. But I don't understand the purpose of the join but it is working.

Why did you close this ? it is a real issue and it still needs to be fixed, i will make a PR when i get some more time so that more people can test and hopefully it will be accepted too

@akfaisel
Also how much speed difference did it make, give some info

the left join is for getting child categories, not sure if you can remove it entirely. Wondering how many articles the site has?

The time for querying has come down from 261642.9 ms to 200 ms and the page loads so fast.

I'm sorry for closing the issue; I shall reopen it.

The issue is not with number of articles but with CATEGORIES. You can also try with the SQL dump I've provided. After installing the dump, just go to Add or Edit Category and it will take at least 4 mins for loading the page.

@akfaisel I have never seen a site with so many categories, what I have seen is a problem with a lot articles that's the reason I am asking. So how many articles you have?

the left join is for getting child categories, not sure if you can remove it entirely. Wondering how many articles the site has?

well you are talking about the join that produces the 'p' table alias and it is that is a different Join:
https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_categories/models/fields/categoryedit.php#L199-L205

// If parent isn't explicitly stated but we are in com_categories assume we want parents
if ($oldCat != 0 && ($this->element['parent'] == true || $jinput->get('option') == 'com_categories'))
{
	// Prevent parenting to children of this item.
	// To rearrange parents and children move the children up, not the parents down.
	$query->join('LEFT', $db->quoteName('#__categories') . ' AS p ON p.id = ' . (int) $oldCat)
		->where('NOT(a.lft >= p.lft AND a.rgt <= p.rgt)');
	...
}

and it is activated in this case of "edit category" and not in the case of "add new category"

i speak of the join that produces the 'b' table alias , in this case it does nothing, because

1. it is LEFT join and not an inner join so it does not limit the returned rows of the query itself (unless the 'b' alias is used in e.g. where)
2. the table alias that it creates is 'b' but is not used anywhere in the query other than the join itself

@akfaisel the original query that you posted looks like its from "add new category"
if you you also test with "edit category"

• does it make the same difference ?
• is anything broken ? (nothing should be, just asking)

nested set model that is used to calculate rgt/lft in core joomla is exponentially slower on inserts or any changes to the tree structure compared to the adjacent model using parent_ids. i've seen sites (on j3) completely crashed upon wrong rgt/lft values at these tables due to various mysql events such as concurret inserts without locks/transactions or caused by bugs in previous joomla versions JTableNested being used too fast (inside foreach)

i already suggested to get rid of the "nested set model" but it would mean lot's of code to get reversed to the old-stable joomla 1.5

mysql itself compared to commercial solutions and my knowledge does not natively support the nested set model.

@ggppdk is a PR coming for testing?

@franz-wohlkoenig
Some reading my comments could make a PR, unfortunately , currently, i don't have time to do it

@ggppdk thanks for Info.

Hi @akfaisel is this is still relevant?
@ggppdk @franz-wohlkoenig

set Status on "Information Required".

If this PR get no Response, it will be closed at 23th July 2017.

@franz-wohlkoenig @rdeutz
i have made a PR ...

thanks @ggppdk

thanks @ggppdk