Member Avatar for kartisathis

hi friends..
I need an alternate query without using UNION for below query..

select DIST_NO from acquired_tbl WHERE  FOLIO_NO='l01917' And STAT_FLAG <> 'Matched' 
union select  DIST_NO from transfrd_tbl WHERE FOLIO_NO='l01917' And STAT_FLAG <> 'Matched'
  • 2 Contributors
  • 3 Replies
  • 86 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by pritaeas

Dani AI

Generated

Short summary and practical advice that follows the thread: wants DIST_NO values from two tables for a specific FOLIO_NO while excluding STAT_FLAG = 'Matched'. tried a JOIN and reported much worse performance. Both observations match common causes: a JOIN can blow up intermediate row counts, while set operators run two narrow scans and then consolidate results.

What to try next (in order of least-invasive to more structural changes):

  • Try the set operator variant that does not do duplicate elimination (faster than a default UNION) if duplicate DIST_NO between the two tables is acceptable. That removes the expensive de-duplication step.
  • Ensure both tables have a covering composite index that starts with FOLIO_NO and includes STAT_FLAG and DIST_NO. With that index the engine can satisfy the query from the index pages without touching table rows.
  • Use EXPLAIN on each candidate query and compare the estimated rows, access type, and whether MySQL uses filesort or temporary. That points to the real cost drivers.
  • If you must avoid any set operator, populate a small temporary (or application-level) table with the two simple scans and then select distinct from it. This separates the scan work from the de-duplication step so you can tune each piece and control engine choices.
  • If the lookup runs frequently, maintain a small summary/materialized table mapping FOLIO_NO to distinct DIST_NO and update it on change.

Notes and cautions: inequality predicates exclude NULLs unless you handle them explicitly. Test with realistic data volumes before picking a final approach.

Recommended Answers

All 3 Replies

Member Avatar for pritaeas

Without a UNION the results will be different. You could try a JOIN on FOLIO_NO.

Member Avatar for kartisathis

ya i tried that too but the performance kills when compared to UNION

Member Avatar for pritaeas

So what is it you're after then ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.