modxcms/revolution — pull request #462 by TriAnMan (2013-03-18 21:13:55+00:00)
Title: [ReUp][#8489] add a new index in the site_tmplvar_contentvalues table

Fix for unoptimal MySQL behavior (prior to 5.1).

This behavior appears on sites with large amount (more than 1000) of resources and corresponding TVs while using getResources to display some pages. Seems that MySQL < 5.1 can't merge indexes on complex queries with LEFT JOINs, eg.:
```sql
SELECT
COUNT(DISTINCT modResource.id)
FROM
modx_site_content modResource
JOIN
modx_site_tmplvar_templates tv_tmpl
ON
modResource.template = tv_tmpl.templateid
JOIN
modx_site_tmplvars tv
ON
tv.id = tv_tmpl.tmplvarid
LEFT JOIN
modx_site_tmplvar_contentvalues tvr
ON
tvr.tmplvarid = tv_tmpl.tmplvarid
AND tvr.contentid = modResource.id
```
Also this commit closes ticket http://tracker.modx.com/issues/8489