joomla/joomla-cms — pull request #4115 by nadeeshaan (2014-08-14 20:46:38+00:00)
Title: Sql optimise new index merged branch

## Testing Instructions:
This PR consists all the PRs merged in to this one regarding the index additions. Two approaches can be followed in order to test this PR. One is in an already installed site and one is for fresh distributions.

#### For already installed sites
For already installed sites first apply the PR and then add follow the bellow commands in order to add the indexes manually to the database

* Index added to #_languages table (*You can see this query execution when you access the site*)

`ALTER TABLE #__languages ADD INDEX 'idx_published_ordering' ('published','ordering');` (#11)

**Related Query Before changes**
```sql
SELECT *
FROM #__languages
WHERE published=1
ORDER BY ordering ASC
```

**Related Query Before changes**
```sql
SELECT lang_id,lang_code,title,title_native,sef,image,description,
metakey,metadesc,sitename,published,access,ordering
FROM #__languages
WHERE published=1
ORDER BY ordering ASC
```
-------------------------------------------------------------------------------------------------------------------------------
* Index added to #_session table (*Query executes every time a user access a page*)

`ALTER TABLE #__session DROP PRIMARY KEY, ADD PRIMARY KEY(session_id(32))` ( #8 )
`ALTER TABLE #__session DROP INDEX time, ADD INDEX time(time(10))`

**Index Description**
This Index added in order to speed up the session update query allowing the sub string index usages

-------------------------------------------------------------------------------------------------------------------------------

* Index added to #_template_styles table (*You can see this query execution when you access the site*)

`ALTER TABLE #__template_styles ADD INDEX idx_client_id('client_id')` ( #13 )

**Index Description**
Without applying this index the below query cannot use any index to retrieve the rows from the database. After applying this index it clearly shows in the explanation section that the query uses the index to retrieve the rows

**Related Query**
```sql
SELECT id, home, template, s.params
FROM #__template_styles as s
LEFT JOIN #__extensions as e
ON e.element=s.template
AND e.type='template'
AND e.client_id=s.client_id
WHERE s.client_id = 0 AND e.enabled = 1
```

-------------------------------------------------------------------------------------------------------------------------------

* Index added to #__contentitem_tag_map table (*You can see this query execution when you access the site*)

`ALTER TABLE #__contentitem_tag_map ADD INDEX idx_alias_item_id ('type_alias','content_item_id')` ( #14 )

**Index Description**
Without applying this index the below query cannot use any index to retrieve the rows from the database. After applying this index it clearly shows in the explanation section that the query uses the index to retrieve the rows. Also one another change did relevant to this index changes. Which is removing the `t.*` from the relevant query and adding the table fields instead. This change can be found (#14) and please refer to that PR also.

**Related Query Before changes**
```sql
SELECT m.tag_id,t.*
FROM #__contentitem_tag_map AS m
INNER JOIN #__tags AS t ON m.tag_id = t.id
WHERE m.type_alias = 'com_content.article' AND m.content_item_id = 5196 AND t.published = 1
AND t.access IN (1,1,5)
```
**Related Query After changes**
```sql
SELECT m.tag_id,'id',t.parent_id,t.lft,t.rgt,t.level,t.path,t.title,t.alias,t.note,t.description,t.published,
t.checked_out,t.checked_out_time,t.access,t.params,t.metadesc,t.metakey,t.metadata,
t.created_user_id,t.created_time,t.created_by_alias,t.modified_user_id,t.modified_time,
t.images,t.urls,t.hits,t.language,t.version,t.publish_up,t.publish_down
FROM #__contentitem_tag_map AS m
INNER JOIN #__tags AS t ON m.tag_id = t.id
WHERE m.type_alias = 'com_content.article' AND m.content_item_id = 5196 AND t.published = 1
AND t.access IN (1,1,5)
```

-------------------------------------------------------------------------------------------------------------------------------

* Index added to #_extensions table (*You can see this query execution when you access the site*)

`ALTER TABLE #__extensions ADD INDEX 'idx_type_ordering' ('type','ordering')` ( #16 )

**Index Description**
Without applying this index the below query uses `filesort` to retrieve the rows from the database, which is an expensive retrieval operation. After applying this index it clearly shows in the explanation section that the query uses the index to retrieve the rows except the `filesort`.

**Related Query**
```sql
SELECT folder AS type, element AS name, params
FROM #_extensions
WHERE enabled >= 1
AND type ='plugin'
AND state >= 0
AND access IN (1,1,5)
ORDER BY ordering
```
-------------------------------------------------------------------------------------------------------------------------------
* Index added to #_menu table (*You can see this query execution when you access the site*)

`ALTER TABLE #__menu ADD INDEX 'idx_client_id_published_lft' ('client_id','published','lft')` ( #17 )

**Index Description**
Without applying this index the below query uses `filesort` to retrieve the rows from the database, which is an expensive retrieval operation. After applying this index it clearly shows in the explanation section that the query uses the index to retrieve the rows except the `filesort`.

**Related Query**
```sql
FROM #_menu AS m
LEFT JOIN #_extensions AS e
ON m.component_id = e.extension_id
WHERE m.published = 1
AND m.parent_id > 0
AND m.client_id = 0
ORDER BY m.lft
```
-------------------------------------------------------------------------------------------------------------------------------
* Index added to #__viewlevels table
(*query Executes when clicking on the add new category in the admin console*)

![Add New Category](https://cloud.githubusercontent.com/assets/1329674/3926574/a08d6d36-23f2-11e4-9e90-57f017940161.png)

`ALTER TABLE #__viewlevels
ADD INDEX `idx_ordering_title` ( `ordering` , `title` )`( #21 )

**Index Description**
Without applying this index the below query uses `filesort` to retrieve the rows from the database, which is an expensive retrieval operation. After applying this index it clearly shows in the explanation section that the query uses the index to retrieve the rows except the `filesort`. Also changed the ` JROOT/libraries/cms/html/access.php ` file's query which is shown below by removing the `GROUP BY` clause since the `PRIMARY KEY` includes in the selected fields. And this reduce the execution time

**Related Query Before Change**
```sql
SELECT a.id AS value, a.title AS text
FROM ltzvy_viewlevels AS a
GROUP BY a.id, a.title, a.ordering
ORDER BY a.ordering ASC,`title` ASC
```

**Related Query After Change**
```sql
SELECT a.id AS value, a.title AS text
FROM ltzvy_viewlevels AS a
ORDER BY a.ordering ASC,`title` ASC
```