joomla/joomla-cms — pull request #4107 by nadeeshaan (2014-08-13 17:04:53+00:00)
Title: Sql optimise access file getassetrules branch

## Testing Instructions:
In order to test this PR, first apply the PR via the Joomla! Patch Tester and then login to the Joomla! site. When accessing the pages where articles are being showed this query triggers.

![Article Pages](https://cloud.githubusercontent.com/assets/1329674/3895721/9599538e-2250-11e4-880f-3c2ed30bf4ed.PNG)

### Original Query before the Optimization

```Sql
SELECT b.rules
FROM #__assets AS a
LEFT JOIN #__assets AS b
ON b.lft <= a.lft
AND b.rgt >= a.rgt
WHERE (a.name = 'com_content')
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lft
```
### After the Optimization

#### Decomposed Query 1:

```sql
SELECT lft,rgt
FROM #__assets
WHERE (name = 'com_content')
```

#### Decomposed Query 2:

```sql
SELECT rules,lft
FROM #__assets
WHERE lft <= retrieved_lft_value
AND rgt >= retrieved_rgt_value
```

### Query Change Description

As we compare the previous query and the decomposed queries you can clearly see that the JOIN operation of the previous query has been removed not using the sub selects, but first retrieving the required `lft` and `rgt` values from the `#__assets` and then use these values normally to retrieve the rules values from the `#_assets` table using a simple select query