[#4114] - Sql optimise admin get list of tags branch
- Closed
- 18 Oct 2014
- Medium
- Build: staging
- # 4114
- Diff
- nadeeshaan:SqlOptimise_admin_get_list_of_tags_branch
- Error The Travis CI build could not complete due to an error Details
User tests: Successful: Unsuccessful:
Testing Instructions:
In order to check the query execution log in to the admin console and click add new category. Then the query triggers.
Original Query before the Optimization
SELECT a.id AS value, a.path, a.title AS text, a.level, a.published
FROM ltzvy_tags AS a
LEFT JOIN `ltzvy_tags` AS b
ON a.lft > b.lft
AND a.rgt b.lft
AND a.rgt alias` 'root'
AND a.published IN (0,1)
ORDER BY a.lft ASC
Query Change Description
In this query GROUP BY clause has been removed and instead added the 'SELECT DISTINCT' clause in order to optimize the query execution
Votes
jissues-bot
- | Status | Pending | ⇒ | New |
| Labels |
Added:
?
?
|
||
| Labels |
Added:
?
Removed: ? |
||
@test success
Before patch: 3.91
After patch: 0.35
You may blame the J!Tracker Application for transmitting this comment.
infograf768
- | Labels |
Added:
?
|
||
for the query refactoring
Wondering if instead of:
AND a.published IN (0,1)
AND a.published >=0 AND a.published <= 1
(with using the possibility to remove one of the 2 compares, depending on the 3 possible values of published). And if that's possible, then a 2-columns index on published, lft would do miracles on that query on large sets.
Could further speedup the query (with lots of categories). In earlier MySQL versions it was the case.
Also if WHEREa.alias<> 'root' is equivalent to WHEREa.id> 0 it could give it another boost.
Also if
WHEREa.alias<> 'root'is equivalent toWHEREa.id> 0it could give it another boost.
for readabilty i prefer WHERE a.lft >0 means more in nested set term
| Labels |
Removed:
?
|
||
| Status | New | ⇒ | Pending |
| Category | ⇒ | SQL |
| Status | Pending | ⇒ | Ready to Commit |
phproberto
- | Labels |
Added:
?
|
||
| Status | Ready to Commit | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2014-10-08 09:08:09 |
Hi,
This has broken tags on postgres even more (I've been working through some of PBF to try and fix some issues in it). Can you guys please investigate?
| Labels |
Added:
?
|
||
George, I think we might have to open a new issue since this one's already committed. Or we'll have to revert.
I'll reopen for now until a committer can review.
| Status | Closed | ⇒ | New |
Yeah if this doesn't get fixed we'll need to revert. This stops any tags from being retrieved for the parent field when creating a new tag in postgres (admittedly you still can't save a tag because of a unrelated SQL error. but small steps right :P)
Setting to Needs Review
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/4114.
| Status | New | ⇒ | Needs Review |
| Status | Needs Review | ⇒ | Closed |
| Closed_Date | 2014-10-08 09:08:09 | ⇒ | 2014-10-18 15:23:32 |
| Rel_Number | ⇒ | 4842 | |
| Relation Type | ⇒ | Related to |
Related Tracker Item- http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=34073&start=0