New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add compound index on modTemplateVarResource #8489

Closed
andchir opened this Issue Sep 5, 2012 · 6 comments

Comments

Projects
None yet
3 participants
@andchir

andchir commented Sep 5, 2012

andchir created Redmine issue ID 8489

800 resources

without tvFilters
php time 1,6226 s

&tvFilters=param===value
php time 8,6630 s

Remove of the sub-queries when tvFilters. Replace by individual queries.

@TriAnMan

This comment has been minimized.

Show comment
Hide comment
@TriAnMan

TriAnMan Dec 30, 2012

Contributor

trianman submitted:

This issue is actual only for MySQL prior to 5.1. I've tested on 5.0.96, 5.1.66 and 5.5.29.

For fix it you have to update MySQL scheme this way:

ALTER TABLE `modx_site_tmplvar_contentvalues` 
    ADD INDEX `tv_cnt` (`tmplvarid` ASC, `contentid` ASC);

Also this index makes such queries about 30% faster on MySQL 5.1.66 and 5.5.29

I wish this fix will be included in further versions of modx.

Contributor

TriAnMan commented Dec 30, 2012

trianman submitted:

This issue is actual only for MySQL prior to 5.1. I've tested on 5.0.96, 5.1.66 and 5.5.29.

For fix it you have to update MySQL scheme this way:

ALTER TABLE `modx_site_tmplvar_contentvalues` 
    ADD INDEX `tv_cnt` (`tmplvarid` ASC, `contentid` ASC);

Also this index makes such queries about 30% faster on MySQL 5.1.66 and 5.5.29

I wish this fix will be included in further versions of modx.

@TriAnMan

This comment has been minimized.

Show comment
Hide comment
@TriAnMan

TriAnMan Dec 30, 2012

Contributor

trianman submitted:

Using &depth=0 when applicable in getResources also speedups page generation about 50%

Contributor

TriAnMan commented Dec 30, 2012

trianman submitted:

Using &depth=0 when applicable in getResources also speedups page generation about 50%

@TriAnMan

This comment has been minimized.

Show comment
Hide comment
@TriAnMan

TriAnMan Jan 1, 2013

Contributor

trianman submitted:

I think this tread have to be into the MODx project ( http://tracker.modx.com/projects/revo )
Could anybody move it?

Contributor

TriAnMan commented Jan 1, 2013

trianman submitted:

I think this tread have to be into the MODx project ( http://tracker.modx.com/projects/revo )
Could anybody move it?

@TriAnMan

This comment has been minimized.

Show comment
Hide comment
@TriAnMan

TriAnMan Mar 19, 2013

Contributor

trianman submitted:

Have made patch for this bug: #462

Contributor

TriAnMan commented Mar 19, 2013

trianman submitted:

Have made patch for this bug: #462

@opengeek

This comment has been minimized.

Show comment
Hide comment
@opengeek

opengeek Mar 21, 2013

Member

opengeek submitted:

Not having a compound index on the tmplvarid and contentid fields in modTemplateVarResource is causing extremely slow queries on larger datasets, especially with older versions of MySQL. Adding this index improves efficiency of many TV related queries in the core on all versions of MySQL.

Member

opengeek commented Mar 21, 2013

opengeek submitted:

Not having a compound index on the tmplvarid and contentid fields in modTemplateVarResource is causing extremely slow queries on larger datasets, especially with older versions of MySQL. Adding this index improves efficiency of many TV related queries in the core on all versions of MySQL.

@opengeek

This comment has been minimized.

Show comment
Hide comment
@opengeek

opengeek Mar 22, 2013

Member

opengeek submitted:

See 63f45d3 for commit details

Member

opengeek commented Mar 22, 2013

opengeek submitted:

See 63f45d3 for commit details

enigmatic-user pushed a commit to enigmatic-user/revolution that referenced this issue Feb 13, 2014

[modxcms#8489] Add compound index to modTemplateVarResource
[ReUp][modxcms#8489] add a new index in the site_tmplvar_contentvalues table

(cherry picked from commit d90c233)

danyaPostfactum pushed a commit to danyaPostfactum/revolution that referenced this issue Mar 26, 2014

Merge branch 'release-2.2' into develop
* release-2.2:
  Make caching aliasMap optional to reduce memory usage
  [modxcms#9672] Fix invalid ini_get call in modDbRegister
  [modxcms#8489] Add compound index to modTemplateVarResource

Conflicts:
	core/docs/changelog.txt

This issue was closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment