Improvement to SQL for loading CategoryId

8 views
Skip to first unread message

Dmitry A.

unread,
Jun 11, 2012, 2:01:45 PM6/11/12
to in-por...@googlegroups.com
Hello,


Currently we have the following SQL (actual ids are not important here) to select/find ID of currently loaded Category.

 SELECT CategoryId
    FROM Categories
    WHERE ( (NamedParentPath = 'index') OR (NamedParentPath = 'Content/index') OR (`Type` = 2 AND CachedTemplate = 'index') ) AND (ThemeId = 8 OR ThemeId = 0)

This can become a problem when you have high number of Categories/Sections (ie. 3K+, but not a problem for small websites) since NamedParentPath is a TEXT field and can't be properly cached.

It would be quite an improvement if we start using Hash (int) column which can be Indexed quite well.

Additionally we might want to INDEX Type field too!


DA

Dmitry A.

unread,
Jun 12, 2012, 12:03:15 AM6/12/12
to in-por...@googlegroups.com
Attaching another proof that we need to do something about it.

In addition to prev. post - shouldn't we cache tag/processing results internally or something. Seems like SQL ran 3 times with exactly the same requests.

DA
In-Portal_--_SQL.png

Alexander Obuhovich

unread,
Jun 12, 2012, 5:30:32 AM6/12/12
to in-por...@googlegroups.com
Here is the task: http://tracker.in-portal.org/view.php?id=1320 
--
Best Regards,

http://www.in-portal.com
http://www.alex-time.com

Alexander Obuhovich

unread,
Jun 12, 2012, 5:43:46 AM6/12/12
to in-por...@googlegroups.com
SQL you described happens from CategoriesEventHandler::_getPassedStructureID method. But inside it it's cached and is never repeated.

I've tried several times, but I'm unable to reproduce that.

Alexander Obuhovich

unread,
Jun 12, 2012, 12:21:26 PM6/12/12
to in-por...@googlegroups.com
Here is a patch, ready for testing.
reversed_template_detection_improvement.patch

Dmitry A.

unread,
Jun 12, 2012, 4:49:19 PM6/12/12
to in-por...@googlegroups.com
Thank you Alex for the Patch!

The upgrade does add new Hash Columns, but values are not populated. Would you please look into this?

DA

Dmitry A.

unread,
Jun 12, 2012, 4:54:31 PM6/12/12
to in-por...@googlegroups.com
New task created for 5.2.0

1321: Category Template Hash fields are not Populated during Upgrade - http://tracker.in-portal.org/view.php?id=1321


DA

Alexander Obuhovich

unread,
Jun 13, 2012, 10:13:36 AM6/13/12
to in-por...@googlegroups.com
Can't replicate. I've did clean 5.1.3 install and upgraded it to 5.2.0-RC1. All hashes were there.

Dmitry A.

unread,
Jul 18, 2012, 12:32:45 AM7/18/12
to in-por...@googlegroups.com
Quick note that Alex was able to reproduce this, while I weren't able to reproduce issue for following 2 reasons:

1. if you had "Structure & Data" section active before doing an upgrade then going to that section after admin login issued automatic rebuild
2. upgrade script was created, but not commited or included in patch

Missing code is committed and patch attached in the task.

DA
Reply all
Reply to author
Forward
0 new messages