[#9160] - Accelerate com_content by removing subquery with badcats and use instead a new c.path_published column
- Closed
- 9 Mar 2016
- Medium
- Build: staging
- # 9160
- Diff
- csthomas:badcats
- Failure continuous-integration/travis-ci/pr The Travis CI build failed Details
User tests: Successful: Unsuccessful:
Column category path_published is a calculation of all parents and own category published.
I have experience with big joomla 3 website and a lots of time I try to improve performance.
This is one of that.
You can see performance improvement on featured or category view especially.
All calculation of category state is calculated when administrator change state of category.
Front end sql check only one column - c.path_published.
Examples of category paths (after calculation):
- Root(published=1, path_published=1)->Category1(published=1, path_published=1)
(... - represent about line) - ... Category2(published=-2, path_published=-2)->Category3(published=1, path_published=-2)
- ... Category2(published=2, path_published=2)->Category3(published=1, path_published=2)
- ... Category2(published=0, path_published=0)->Category3(published=1, path_published=0)
- ... Category2(published=2, path_published=2)->Category3(published=0, path_published=0)
- ... Category2(published=2, path_published=2)->Category3(published=1, path_published=2)
Priority for calculation state for path_published in table #__categories:
- the most priority has Trash if published (-2), then all children will have path_published -2
- the next is Unpublished if published (0), then all children may have path_published -2 or 0
- the next is Archive if published (2), then all children may have path_published -2,0 or 2
- the last one is Published (1)
Please comment.
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Labels |
Added:
?
|
||
| Category | ⇒ | SQL |
Tests are not something I can help you with but the CI reports the issue as
There was 1 error:
1) JComponentRouterViewTest::testGetPath
PDOException: SQLSTATE[HY000]: General error: 1 no such column: c.path_published
/home/travis/build/joomla/joomla-cms/libraries/joomla/database/driver/pdo.php:723
/home/travis/build/joomla/joomla-cms/libraries/legacy/categories/categories.php:302
/home/travis/build/joomla/joomla-cms/libraries/legacy/categories/categories.php:184
/home/travis/build/joomla/joomla-cms/tests/unit/suites/libraries/cms/component/router/stubs/JComponentRouterViewInspector.php:59
/home/travis/build/joomla/joomla-cms/libraries/cms/component/router/view.php:116
/home/travis/build/joomla/joomla-cms/tests/unit/suites/libraries/cms/component/router/JComponentRouterViewTest.php:139
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9160.
The CI reports:
PDOException: SQLSTATE[HY000]: General error: 1 no such column: c.path_published
but I added a new column in:
- installation/sql/mysql/joomla.sql
- installation/sql/postgresql/joomla.sql
- installation/sql/sqlazure/joomla.sql
Where should I add it too?
| Labels |
Added:
?
|
||
Thanks @mbabker
- I added changes to ddl.sql and postresql.sql.
- mysql.sql does not contain any table structures so I did not change it.
- sqlsrv.sql is probably very old and had EOL problem, MAC version? I converted it to "\n" linux version and I added my changes.
Now I can not understand what is the problem with the tests.
I have found that my pull request is not very useful for joomla 3 after was added "publish cascade".
Should I close it then?
I close it.
| Status | Pending | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-03-09 09:12:22 |
| Closed_By | ⇒ | csthomas |
In short: it moves computing of publish state on category tree from view to administrator what should be more reasonable.
This is valuable patch, but complicated.
If someone have problem with slow featured view on home page or on big category view this patch can help.
Subquery with badcats eats more memory when getting a lots of articles.
I talk about #__content table with more than 100K articles where lots of are featured.
I had problems with a lots of queries from articles.php model that fails because of mysql memory problems and returns NULL
then I wrote that patch and one another at
#9161
I used that patches in joomla 2.5 and I use in joomla 3.4 on production server (mysql only) and have not got any problems.
Can I ask someone to point me where is a problem with tests?
I changed installation sql for mysql/postresql/sqlazure.
Where should I change code to pass tests?
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/9160.