Pull Request for fix conflict of #4115 .

Summary of Changes

conflict fixed and added mssql and postgresql compatibility

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

  SELECT *
  FROM #__languages
  WHERE published=1
  ORDER BY ordering ASC

Related Query Before changes

  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

    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

    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

    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

    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

    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)

ALTER TABLE #__viewlevels ADD INDEXidx_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

    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

    SELECT a.id AS value, a.title AS text
    FROM ltzvy_viewlevels AS a
    ORDER BY a.ordering ASC,`title` ASC

Testing Instructions