Joomla! Issue Tracker - CMS

Download
Launch
Feeling Lucky

[#4112] - Sql optimise admin menu types branch

? Success

User tests: Successful: Unsuccessful:

avatar nadeeshaan
nadeeshaan
14 Aug 2014

Testing Instructions:

In order to check the query execution log in to the admin console and click menu manager. Then the query triggers. To check how the PR works, first apply the PR via the Joomla! Patch Tester and then log in to the admin console and click menu manager.

Menu Manager

Original Query before the Optimization

    SELECT a.*
    FROM #_menu_types AS a
    GROUP BY a.id, a.menutype, a.title, a.description
    ORDER BY a.id asc

After the Optimization

    SELECT a.id, a.menutype, a.title, a.description
    FROM #_menu_types AS a
    WHERE a.id > 0
    ORDER BY a.id asc

Query Change Description

In this query group by clause has been removed. Also removed the SELECT a.* and instead added the corresponding fields to the select clause. Also added the WHERE clause in order to be able to use the PRIMARY KEY as the index.

avatar nadeeshaan nadeeshaan - open - 14 Aug 2014
avatar jissues-bot jissues-bot - change - 14 Aug 2014
Status Pending ⇒ New
Labels Added: ? ?
avatar brianteeman brianteeman - change - 14 Aug 2014
Labels Added: ?
Removed: ?
avatar zero-24
zero-24 - comment - 14 Aug 2014

@test

Test results

Bevor patch: 0.70 ms
After patch: 0.39 ms

thanks @nadeeshaan :+1:

avatar 810
810 - comment - 14 Aug 2014

@test

Test results

Before patch: 0.99 ms
After patch: 0.73 ms

  • Success

thanks @nadeeshaan

avatar beat
beat - comment - 15 Aug 2014

I don't think that adding WHERE a.id > 0 brings anything in terms of performance. The ORDER BY clause will automatically trigger the use of the right (primary) index imho.

avatar brianteeman
brianteeman - comment - 16 Aug 2014

@test
Tested with 1178 categories and 57345 articles and 59883 assets (created by com_overload)
before 0.39ms
after 0.23ms

screenshot 2014-08-16 11 20 20
screenshot 2014-08-16 11 22 05

avatar nicksavov nicksavov - change - 21 Aug 2014
Labels Removed: ?
avatar brianteeman brianteeman - change - 21 Aug 2014
Status New ⇒ Pending
avatar brianteeman brianteeman - change - 23 Sep 2014
Category ⇒ SQL
avatar twssachin
twssachin - comment - 18 Oct 2014

@test
Before Patch: Query Time: 30.61 ms After last query: 165.09 ms Query memory: 0.012 MB Memory before query: 1.154 MB
After Patch: Query Time: 0.37 ms After last query: 0.39 ms Query memory: 0.011 MB Memory before query: 1.168 MB

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4112.

avatar Hackwar Hackwar - change - 18 Oct 2014
Status Pending ⇒ Ready to Commit
avatar Hackwar
Hackwar - comment - 10 Feb 2015

We have several successfull tests and no negative ones. Setting this one to RTC. @beat Your concern might be valid, but it also has not negative impact, so I wouldn't stop this PR from being merged.

This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4112.
avatar brianteeman brianteeman - change - 18 Feb 2015
Labels Added: ?
avatar mbabker mbabker - change - 15 Mar 2015
Status Ready to Commit ⇒ Closed
Closed_Date 0000-00-00 00:00:00 ⇒ 2015-03-15 16:43:56
avatar mbabker mbabker - close - 15 Mar 2015
avatar mbabker mbabker - reference | - 15 Mar 15
avatar mbabker mbabker - merge - 15 Mar 2015
avatar mbabker mbabker - close - 15 Mar 2015

Add a Comment

Login with GitHub to post a comment