mysql workbench takes too long time to apply a simple query

Question

this is my query,

    SELECT 
    COUNT(a.ref) AS refoct2015,
    COUNT(b.ref) AS refspet2015,
    COUNT(c.ref) AS refoct2014,
    port_ID
FROM
    report AS a
        JOIN
    report_sept2015 AS b USING (port_ID)
        JOIN
    report_oct2014 AS c USING (port_ID)
GROUP BY port_ID , a.booking_date , b.booking_date , c.booking_date

but sql takes more than 15 min to give me a result when it doesn't stop working. I restart it many times, changed the reading time (Edit>Preference>etc..) but nothing still very slow

Answer 1

If you need the count before the joins occur then you have to generate the count before you join.

This is one method to accomplish that:

SELECT A.Port_ID, A.refoct2015, B.refSept2015 , C.RefOct2014 
FROM (SELECT Port_ID, Booking_Date, count(Ref) as refoct2015 
      FROM report
      GROUP BY Port_Id, Booking_Date) A
INNER JOIN 
     (SELECT Port_ID, Booking_Date, count(Ref) as refSept2015 
      FROM Report_sept2015
      GROUP BY Port_Id, Booking_Date) B 
  ON A.Port_ID = B.Port_ID
INNER JOIN 
     (SELECT Port_ID, Booking_Date, count(Ref) as RefOct2014 
      FROM report_oct2014
      GROUP BY Port_Id, Booking_Date) C 
  ON B.Port_ID = C.Port_ID

Though it seems odd to include booking_date and not join on it...

If I have in table A 100 distinct dates and in table B 100 distinct dates, and in table c 100 distinct dates, then your result set would be 100*100*100 assuming each share the same port_Id... probably why its taking 15 minutes... Sure you're not missing a join?

This seems to make more sense to me...

SELECT A.Port_ID, A.refoct2015, B.refSept2015 , C.RefOct2014 
FROM (SELECT Port_ID, Booking_Date, count(Ref) as refoct2015 
      FROM report
      GROUP BY Port_Id, Booking_Date) A
INNER JOIN 
     (SELECT Port_ID, Booking_Date, count(Ref) as refSept2015 
      FROM Report_sept2015
      GROUP BY Port_Id, Booking_Date) B 
  ON A.Port_ID = B.Port_ID
 and A.Booking_date= B.Booking_date
INNER JOIN 
     (SELECT Port_ID, Booking_Date, count(Ref) as RefOct2014 
      FROM report_oct2014
      GROUP BY Port_Id, Booking_Date) C 
  ON B.Port_ID = C.Port_ID
 and A.Booking_date= B.Booking_date

However this implies that if dates and port IDs are not in all tables then you'd have a problem. with data elimination due to the inner join. Normally I'd use a Full outer join to resolve this but mySQL doesn't support it. You'd have to use Outer joins and unions to simulate the behavior.

So rather than continue to guess at what you're after. Explain what those tables are for and what you're trying to do accomplish in plain English; in your original question by editing it.

I'm afraid we're falling into the XY problem here

Why is booking_date relevant if it's not in the select?

It really seems like you're trying to pivot the data from these three tables by date...but then not showing date....

Are you after something like

Port_Id   Date    Oct2015  Sept2015  Oct2014
1        1/1/2015     500       250        0
1        1/2/2015     100        60        0
2        1/1/2015     100       540       60

?