Selec All Records, MySQL

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;


absolute service time: 0,39 sec