View Issue Details

IDProjectCategoryView StatusLast Update
0004925OXID eShop (all versions)4.08. Cachepublic2013-02-13 09:35
Reportermark Assigned To 
PriorityhighSeveritymajorReproducibilityalways
Status resolvedResolutionfixed 
Product Version4.7.3 / 5.0.3 revision 54408 
Fixed in Version4.7.4 / 5.0.4 revision 57063 
Summary0004925: oxutilscount::setCatArticleCount uses ugly sql statement (filesort, temp tables)
Descriptionoxutilscount::setCatArticleCount builds a sql query like

SELECT count(*) FROM (
                   SELECT count(*) FROM
[...]
GROUP BY oxv_oxarticles_4_de.oxid
                   ) AS ox2cat

Which does unneccessary operations in database (creating temporary tables, doing filesort).
Steps To Reproduce1) insert echo $sQ in oxutilscount::setCatArticleCount
2) delete aLocalCatCache-File from tmp and open Shop
3) take query and do "EXPLAIN {QUERY}" in Database

delete "SELECT count(*) FROM (" and ") AS ox2cat" from start and ending of query, change "SELECT count(*)" to "SELECT count(DISTINCT oxv_oxarticles_4_de.oxid) as cnt FROM" (replace my VIEWNAME.OXID through part that was in GROUP BY before).

Now again make EXPLAIN - no temporary tables will be opened.
Additional InformationAnother question regarding this -> how is an update request (invalidating) done in environments with more than one app-server?

Can be reproduced in 4.5.1 too
TagsNo tags attached.
ThemeBoth
BrowserAll
PHP Version5.3
Database Versionany

Activities

mark

2013-02-12 19:56

reporter   ~0008402

Sorry, forgot to mention: remove GROUP BY oxv_oxarticles_4_de.oxid to

mark

2013-02-13 09:01

reporter   ~0008405

SELECT count(DISTINCT oxv_oxarticles_4_de.oxid) as cnt FROM

=>

SELECT count(DISTINCT oxv_oxarticles_4_de.oxid) as ox2cat FROM

Linas Kukulskis

2013-02-13 09:35

reporter   ~0008406

fixed query