Mysql - SQL queries for counter web visits per day, month, year and totals

Question

I am making a system to count visits my website and then display it on a graph chart type.

Example, I want to get all visits by:

1. Total web visitors today

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE DATE_FORMAT(create_at, '%Y-%m-%d') = CURDATE()) AS total_today

2. Total visits web for an hour

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)) AS total_before_hours

3. Total site visits yesterday

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)) AS total_yesterday

4. Total visits site of the week

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE YEARWEEK(create_at, 1) = YEARWEEK(CURDATE(), 1)) AS total_week

5. Total visits website last week

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at >= DATE_SUB(NOW(), INTERVAL 1 WEEK)) AS total_last_weekend

6. Total site visits Month

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE MONTH(create_at) = MONTH(NOW())) AS total_month

7. Total visits Web last month

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS total_last_month

8. Total Web visits all year

SELECT COUNT(DISTINCT ip) FROM visits_website WHERE YEAR(create_at) = YEAR(CURDATE())) AS total_year

I am registering visits the website in a MySql table, and I want to get this table all visits the web with different IP received in specified period, I have held several consultations with the function MySql [DATE_SUB], because had to change several times for some queries:

These are the query I'm doing for all visits:

SELECT 
COUNT(DISTINCT ip) AS total,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at  >= DATE_SUB(NOW(), INTERVAL 1 HOUR)) AS total_before_hours,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE DATE_FORMAT(create_at, '%Y-%m-%d') = CURDATE()) AS total_today,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at  >= DATE_SUB(NOW(), INTERVAL 1 DAY)) AS total_yesterday,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE YEARWEEK(`create_at`, 1) = YEARWEEK(CURDATE(), 1)) AS total_week,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at  >= DATE_SUB(NOW(), INTERVAL 1 WEEK)) AS total_last_weekend,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE MONTH(`create_at`) = MONTH(NOW())) AS total_month,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE create_at  >= DATE_SUB(NOW(), INTERVAL 1 MONTH)) AS total_last_month,
(SELECT COUNT(DISTINCT ip) FROM visits_website WHERE YEAR(`create_at`) = YEAR(CURDATE())) AS total_year
FROM visits_website

I want to know the following:

1. It is these optimal mysql queries and the best way to make this system?
2. It is a good development practice, more indexes are needed in the tables.

Table code:

CREATE TABLE IF NOT EXISTS `visits_website` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT,
    `ip` VARCHAR(25) NOT NULL,
    `browser_short` VARCHAR(45) NOT NULL,
    `browser_long` VARCHAR(255) NOT NULL,
    `create_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_visits_website` (`ip`)
)Engine=InnoDB;

INSERT INTO `visits_website` VALUES ('1', 'ip1', 'ip1', '', '2016-08-31 20:30:00');
INSERT INTO `visits_website` VALUES ('2', 'ip1', 'ip1', '', '2016-08-31 20:30:00');
INSERT INTO `visits_website` VALUES ('3', 'ip2', 'ip2', '', '2016-08-31 19:30:00');
INSERT INTO `visits_website` VALUES ('4', 'ip1', 'ip1', '', '2016-08-31 19:30:00');
INSERT INTO `visits_website` VALUES ('5', 'ip2', 'ip2', '', '2016-08-31 18:30:00');
INSERT INTO `visits_website` VALUES ('6', 'ip3', 'ip3', '', '2016-08-31 18:30:00');
INSERT INTO `visits_website` VALUES ('7', 'ip1', 'ip1', '', '2016-08-31 17:30:00');
INSERT INTO `visits_website` VALUES ('8', 'ip2', 'ip2', '', '2016-08-31 17:30:00');
INSERT INTO `visits_website` VALUES ('9', 'ip3', 'ip3', '', '2016-08-31 16:30:00');
INSERT INTO `visits_website` VALUES ('10', 'ip4', 'ip4', '', '2016-08-31 16:30:00');
INSERT INTO `visits_website` VALUES ('11', 'ip1', 'ip1', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('12', 'ip2', 'ip2', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('13', 'ip3', 'ip3', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('14', 'ip4', 'ip4', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('15', 'ip5', 'ip5', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('16', 'ip1', 'ip1', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('17', 'ip2', 'ip2', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('18', 'ip3', 'ip3', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('19', 'ip4', 'ip4', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('20', 'ip5', 'ip5', '', '2016-08-30 20:30:00');
INSERT INTO `visits_website` VALUES ('21', 'ip6', 'ip6', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('22', 'ip1', 'ip1', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('23', 'ip2', 'ip2', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('24', 'ip3', 'ip3', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('25', 'ip4', 'ip4', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('26', 'ip5', 'ip5', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('27', 'ip6', 'ip6', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('28', 'ip7', 'ip7', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('29', 'ip1', 'ip1', '', '2016-08-29 20:30:00');
INSERT INTO `visits_website` VALUES ('30', 'ip2', 'ip2', '', '2016-08-29 20:30:00');

I much appreciate your help.

Answer 1

This is a very long question. One key piece of advice on this kind of DATETIME - based summary generation: Make your queries sargable -- make them able to use an index.

For example:

 SELECT COUNT(DISTINCT ip)
   FROM visits_website
  WHERE MONTH(create_at) = MONTH(NOW()  /* Slow! */

is not sargable, because it applies a function (MONTH()) to a column in the table. MySQL will have to examine every row of the table to satisfy this query. That will be slooooow. Instead try this, to look for all the visits in the present month.

 SELECT COUNT(DISTINCT ip)
   FROM visits_website
  WHERE create_at >= LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
    AND create_at < LAST_DAY(NOW()) + INTERVAL 1 DAY

This works because it searches a range of DATETIME values from the beginning of the present month up until but not including the beginning of next month.

Then, create a compound covering index on (create_at, ip) and your queries should work well. MySQL can scan the index range it needs.

Notice that this all works fine for TIMESTAMP data as well.