18128

Retrieving the rows using join query

I have two tables like this

A B ---- ------- col1 col2 col1 col2 ---------- -----------

A table contains 300k rows B table contains 400k rows

I need to count the col1 for table A if it is matching col1 for table B I have written a query like this:

select count(distinct ab.col1) from A ab join B bc on(ab.col1=bc.col1)

but this takes too much time

Answer1:

could try a group by... Also ensure that the col1 is indexed in both tables

SELECT COUNT (col1 ) FROM ( SELECT aa.col1 FROM A aa JOIN B bb on aa.col1 = bb.col1 GROUP BY (aa.col1) )

Answer2:

It's difficult to answer without you positing more details: did you analyze the tables? Do you have an index on col1 on each table? How many rows are you counting?

That being said, there aren'y so many potential query plans for your query. You likely have two seq scans that are hash joined together, which is about the best you can do... If you've a material numbers of rows, you'll be counting a gazillion rows, and this takes time.

Perhaps you could rewrite the query differently? If every B.col1 is in A.col1, you could get the same result without the join:

select count(distinct col1) from B

If A has low cardinality, it might be faster to rely on exists():

with vals as ( select distinct A.col1 as val from A ) select count(*) from vals where exists(select 1 from B where B.col1 = vals.val)

Or, if you know every possible value from A.col1 and it's reasonably small, you could unnest an array without querying A at all:

select count(*) from unnest(Array[val1, val2, ...]) as vals (val) where exists(select 1 from B where B.col1 = vals.val)

Or vice-versa, in each of the above, if every B holds the reference values.

Recommend

  • Select Statement on Two different views
  • SQL query to find a specific value for all records
  • update table from another table in db2 9
  • How to create a dollar amount input field in an HTML form?
  • What is the difference between tfpt scorch and tfpt treeclean
  • Why does this code crash on the distributed app but work in the debugger?
  • C# helper classes to implement NTLM authorization
  • Column Nullability/Optionality: NULL vs NOT NULL
  • How can i compile & run a c program (with OpenMP) in gem5 Full System?
  • Regexp, capture between parentheses, javascript
  • ThreadPool behaviour: not growing from minimum size
  • How to return a number as a binary string with a set number of bits in python
  • Importing Excel files with a large number of columns header into mysql with c#
  • Log4j cannot find properties file in a simple application
  • Using an enum contained in a Cloud Endpoint model on a Android client
  • Express displaying mongodb documents in Jade
  • MySQL: Difference between `… ADD INDEX(a); … ADD INDEX(b);` and `… ADD INDEX(a,b);`?
  • Ruby and class variables in inherit class
  • How to display special characters in SQL server 2008?
  • Activation Function choice for Neural network
  • Android Studio Can't Find tools.jar
  • UWP/C# - Issue with AQS and USB Devices
  • Using Sax parsing to edit and write XML in VB6
  • Ensure fsync did its job
  • Django: Count of Group Elements
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Does CUDA 5 support STL or THRUST inside the device code?
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • How do you join a server to an Active Directory (domain)?
  • Memory offsets in inline assembly
  • embed rChart in Markdown
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • How does Linux kernel interrupt the application?
  • How can I remove ASP.NET Designer.cs files?
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]