[#4107] - Sql optimise access file getassetrules branch
- Closed
- 6 Apr 2015
- Medium
- Build: staging
- # 4107
- Diff
- nadeeshaan:SqlOptimise_access_file_getassetrules_branch
- Success The Travis CI build passed Details
User tests: Successful: Unsuccessful:
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.
Original Query before the Optimization
SELECT b.rules
FROM #__assets AS a
LEFT JOIN #__assets AS b
ON b.lft = a.rgt
WHERE (a.name = 'com_content')
GROUP BY b.id, b.rules, b.lft
ORDER BY b.lftAfter the Optimization
Decomposed Query 1:
SELECT lft,rgt
FROM #__assets
WHERE (name = 'com_content')Decomposed Query 2:
SELECT rules,lft
FROM #__assets
WHERE lft = retrieved_rgt_valueQuery 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
jissues-bot
- | Status | Pending | ⇒ | New |
| Labels |
Added:
?
?
|
||
| Labels |
Added:
?
Removed: ? |
||
infograf768
- | Labels |
Added:
?
|
||
@nadeeshaan Thanks, good direction too, looking at description above only:
You could probably still have a single query using sub-query.
There is one thing that got missing on the rewrite and that could be important for the authorizations:ORDER BY b.lft : that is most probably needed to get authorization tree ordered in the correct rules inheritence. So from a security audit perspective NOT yet OK.
nicksavov
- | Labels |
Removed:
?
|
||
| Status | New | ⇒ | Pending |
| Category | ⇒ | SQL |
Two good tests but because of @beat comment I am setting this to needs review
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4107.
| Status | Pending | ⇒ | Needs Review |
the original query was decomposed in two query
the first query don't need an order by lft cause the field name have a UNIQUE CONSTRAINT
SELECT lft,rgt
FROM #__assets
WHERE (name = 'com_content')
the second query have the order by lft
SELECT rules,lft
FROM #__assets
WHERE lft <= retrieved_lft_value
AND rgt >= retrieved_rgt_value
order by lft
so don't understand what is exactly the problem
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4107.
Request for RTC and removal of erroneous label "New Feature", suggest to replace it with "Improvement" or "Optimization".
Agree with @alikon that @beat made a mistake and there is no need for review.
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4107.
We've been discussing this item in the sql-optimisation-wg on Glip and we've issued a new pull request to supersede this one after additional discussion:
#6681
We'd love to have those involved with this pull request to help us test the new pull request so we can hopefully get it merged quickly.
Thank you!
| Status | Needs Review | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2015-04-06 20:32:45 |
@test
Test results
Bevor patch: 4.59 ms
After patch: 0.59 ms and 0.61 ms
So also here full success
thanks @nadeeshaan