Search code examples
mysqlsubquerycorrelated-subquery

Convert correlated mysql subquery to noncorelated

I need a help to convert correlated sub query to noncorrelated, this is a query:

http://sqlfiddle.com/#!2/2a67b/3

Thanks for advice

Solution

  • I am really struggling to work out what your SQL is trying to do in the first place.

    It appears to be getting every second between the min and max seconds (well, 1 second more than the max date time), and the getting a count of records to go with that.

    If so, then coping with up to 1000 seconds:-

    SELECT ADDDATE(MinCallDate, INTERVAL Sub1.i SECOND) AS aDate, COUNT(*)
FROM 
(
    SELECT MIN(calldate) AS MinCallDate, MAX(calldate) AS MaxCallDate
    FROM calls
)Sub0
CROSS JOIN
(
    SELECT units.i + tens.i * 10 + hundreds.i * 100 as i
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
) Sub1
INNER JOIN 
(
    SELECT calldate, ADDDATE(calldate, INTERVAL billsec SECOND) AS callenddate
    FROM calls
)   b
ON DATE_FORMAT(ADDDATE('2013-05-14 09:40:30', INTERVAL Sub1.i SECOND),"%Y%m%d%H%i%s") BETWEEN b.calldate AND b.callenddate
WHERE ADDDATE(MinCallDate, INTERVAL Sub1.i SECOND) <= MaxCallDate
AND Sub1.i < TIMESTAMPDIFF(SECOND,'2013-05-14 09:40:30', '2013-05-14 09:41:00')
GROUP BY aDate

    If you could provide an non SQL explanation of what you are trying to achieve I might be able to come up with something better.

    EDIT - for the simple counts:-

    SELECT Sub1.TimeSecond, COUNT(Sub2.CallSecond)
FROM
(
    SELECT ADDDATE('2013-05-14 09:40:30', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS TimeSecond
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    HAVING TimeSecond BETWEEN '2013-05-14 09:40:30' AND '2013-05-14 09:41:00'
) Sub1
LEFT OUTER JOIN
(
    SELECT ADDDATE(calldate, INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS CallSecond
    FROM calls
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    WHERE units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 <= billsec
) Sub2
ON Sub1.TimeSecond = Sub2.CallSecond
GROUP BY Sub1.TimeSecond

    This will cope ranges / phone calls up to 9999 seconds long (easy to expand it for larger ranges, but that will make it slower. However not sure any of these are going to be efficient as MySQL can't really use indexes for any of the joins.

    Simpler would be this, as it isn't generating each time within each calls duration:-

    SELECT Sub1.TimeSecond, COUNT(calls.calldate)
FROM
(
    SELECT ADDDATE('2013-05-14 09:40:30', INTERVAL units.i + tens.i * 10 + hundreds.i * 100 + thousands.i * 1000 SECOND) AS TimeSecond
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) thousands
    HAVING TimeSecond BETWEEN '2013-05-14 09:40:30' AND '2013-05-14 09:41:00'
) Sub1
LEFT OUTER JOIN calls
ON Sub1.TimeSecond BETWEEN calls.calldate AND ADDDATE(calls.calldate, INTERVAL calls.billsec SECOND)
GROUP BY Sub1.TimeSecond

    Could you change the layout of the table? If so adding a column for call end date time would probably help.