Subversion Repositories SmartDukaan

Last modification

• Rev 36330 2026-04-21 16:10:52
• Author: amit

• Log message:

  Fix getWarehousewiseCollection: add timestamp index, drop misleading FORCE INDEX
  
  EXPLAIN on hadb1 showed the query was full-scanning transaction.userwallethistory
  (1.9M rows) despite FORCE INDEX (idx_uwh_wallet_timestamp). The composite
  (wallet_id, timestamp) needs an equality predicate on wallet_id to be usable,
  but this query filters by uwh.timestamp at the scan level with no wallet_id —
  driven from fofo_store via joins. Optimizer correctly rejected the hint and
  picked ALL. Query averaged 773 ms across 4,095 calls (3,167 s cumulative).
  
  - add_idx_uwh_timestamp_reftype.sql: new index on (timestamp, reference_type)
  so the range scan narrows by time and reference_type filters inline.
  - UserWalletRepositoryImpl:110: drop FORCE INDEX hint; let optimizer choose.
  Updated comment to explain why.
  
  Left PartnerCollectionPlanRepositoryImpl.getCommitmentCollectionSummary:117
  untouched — that query drives from pcp → userwallet → userwallethistory and
  the join has wallet_id = uw.id known at scan, so (wallet_id, timestamp) is
  the right index there; FORCE INDEX remains appropriate.