Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0037113Openbravo ERP03. Procurement managementpublic2017-10-19 10:222018-03-14 12:38
Reportervmromanos 
Assigned ToAtulOpenbravo 
PrioritynormalSeverityminorReproducibilityN/A
StatusnewResolutionopen 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customerNo
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0037113: Index clean up in C_Order table
DescriptionC_ORDER_DOCNO is not needed because C_ORDER_DOCUMENTNO_ID also indexes by DOCUMENTNO as first column.

In C_ORDER_CLIENT_ORG_DATE_DOCNO we can remove AD_CLIENT_ID column because in most of the cases all the orders in the system belong to the same client. This will reduce index size and will improve performance.
Steps To ReproduceNA
Proposed SolutionAttached (remember to export database after applying it)
Additional Information
TagsPerformance
Relationships
related to defect 0035912 closed Sandrahuguet Has no sense to have an index with ad_client_id at fist column 
Attached Filesdiff order_index_review.diff (1,036) 2017-10-19 10:22
https://issues.openbravo.com/file_download.php?file_id=11205&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-10-19 10:22vmromanosNew Issue
2017-10-19 10:22vmromanosAssigned To => Triage Finance
2017-10-19 10:22vmromanosFile Added: order_index_review.diff
2017-10-19 10:22vmromanosOBNetwork customer => No
2017-10-19 10:22vmromanosModules => Core
2017-10-19 10:22vmromanosTriggers an Emergency Pack => No
2017-10-19 10:28vmromanosTarget Version => 3.0PR18Q1
2017-10-19 10:39vmromanosTag Attached: Performance
2017-10-19 11:55shuehnerRelationship addedrelated to 0035912
2017-10-19 12:02shuehnerNote Added: 0099959
2017-10-23 12:56dmiguelezAssigned ToTriage Finance => AtulOpenbravo
2018-02-20 12:44vmromanosTarget Version3.0PR18Q1 => 3.0PR18Q1.1
2018-03-14 12:38aferrazTarget Version3.0PR18Q1.1 =>

Notes
(0099959)
shuehner   
2017-10-19 12:02   
for 2nd index (remove ad_client_id) -> marked as related with issue where we did same change already in 2 other tables (for similar reason)

for 1st index:
- As both indexes are not unique functionally they are equivalent
- However for performance + size the 'single' column one is still better than combined one.

Example for but database with 84M orders:
- c_order_docno 2.592MB size
- c_order_documentno_id 6332MB size

Note: that combined one we need for grid ordering anyway

So in practice not just delete -> better but balance:
- is perf improvement for queries not needing _id part worth having the overhead of
- 2.5gb space + overhead of maintaining that idx

I tend to say not worth having both but don't have hard numbers ready.