temp, MySQL - rextester

Language: Layout:

DROP TABLE log;
DROP TABLE urls;
DROP TABLE campaign;

CREATE TABLE log
(
    id int,
    url_id int,
    ip_address varchar(100),
    date_time date
);

CREATE TABLE campaign
(
    campaign_id int,
    campaign varchar(100)
);

CREATE TABLE urls
(
    url_id int,
    campaign_id int,
    friendly_name varchar(100),
    short_url varchar(100)
);

INSERT INTO urls VALUES
(1, 1, 'test 1', 'test1'),
(2, 2, 'test 2', 'test2'),
(3, 1, 'test 3', 'test3'),
(4, 0, 'test 4', 'test4');

INSERT INTO log VALUES
(1, 1, '127.0.0.1', '2017-03-14'),
(2, 1, '127.0.0.1', '2017-03-15'),
(3, 2, '127.0.0.1', '2017-03-16'),
(4, 2, '127.0.0.1', '2017-03-17');

INSERT INTO campaign VALUES
(1, 'campaign1'),
(2, 'campaign2');

-- SELECT u.url_id, u.short_url, c.campaign, Count(*) AS Clicks
-- FROM (urls u LEFT JOIN log l ON l.url_id = u.url_id) LEFT JOIN campaign c ON u.campaign_id = c.campaign_id
-- WHERE (((DATE_FORMAT(l.date_time, '%Y-%m-%d')) Between '2017-03-14' And '2017-03-17'))
-- GROUP BY u.url_id, u.short_url, c.campaign;

SELECT t1.url_id,
       t1.short_url,
       COALESCE(t2.campaign, 'NA') AS campaign,
       COALESCE(t3.num_clicks, 0) AS Clicks
FROM urls t1
LEFT JOIN campaign t2
    ON t1.campaign_id = t2.campaign_id
LEFT JOIN
(
    SELECT url_id, COUNT(*) AS num_clicks
    FROM log
    WHERE DATE_FORMAT(date_time, '%Y-%m-%d') BETWEEN '2017-03-14' AND '2017-03-17'
    GROUP BY url_id
) t3
    ON t1.url_id = t3.url_id


Absolute service time: 0,33 sec, absolute service time: 0,35 sec