2

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

  1. 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

  1. Total site visits yesterday

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

  1. Total visits site of the week

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

  1. 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

  1. Total site visits Month

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

  1. 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

  1. 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.

Learning and sharing
  • 1,378
  • 3
  • 25
  • 45
  • To run the query for all users that visit for the year seems a little overwhelming. Therefore, there should be some automated crono tasks that build another table to condense the information and reduce the amount of processing required. If you want to keep the query, every 24 hours some of these query could be ran and cached, to reduce the need to run them after every page impression. – hxtree Sep 01 '16 at 17:19
  • If that you have in mind, but also be a section for public area, only for a manager, because I want to do something like this http://jsfiddle.net/gh/get/jquery/1.9.1/highslide-software/highcharts.com/tree/master/samples/highcharts/demo/line-basic/, I was reviewing this website http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/, the most appropriate way to do this but there are many ways, this way I'm doing these consultations, can be considered as a good practice development. – Learning and sharing Sep 01 '16 at 17:30

1 Answers1

5

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.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Dear Jones, :) if it's a very long question, thank you very much for your help, has helped me a lot, it is very logical what you have said, have a great day. – Learning and sharing Sep 01 '16 at 18:34
  • I saw something with the function LAST_DAY() , if a month has 30 days, by adding one day LAST_DAY(NOW()) - INTERVAL 1 MONTH + INTERVAL 1 DAY, you should go to the next month but adds 1 to 30, resulting in 31, and the idea of adding one day is going to next month , as we can fix that, so that by adding one day a month that has 30 days, mysql detect that month has only 30 days, and will add the next month instead of adding value to 30 Mysql may detect that? – Learning and sharing Sep 01 '16 at 21:52
  • You are correct. I edited. `- INTERVAL 1 MONTH` only moves to the first day of the previous month if it is already on the first day of a month. `LAST_DAY('2016-02-27') + INTERVAL 1 DAY - INTERVAL 1 MONTH` gets `2016-02-01`, but `LAST_DAY('2016-02-27') - INTERVAL 1 MONTH + INTERVAL 1 DAY` gets `2016-01-30`, which is wrong. – O. Jones Sep 01 '16 at 21:59
  • I need to select all web visits from the database, for example last week, last month, last hour, for this I have two ways to do this, one would add an additional field in the database to store the date as a date('Y-m-d'), and then with accuracy leccioanr last week that with NOW('Y-m-d H:i:s'), the select takes into account the hours, minutes and seconds. I can do it this way, but would have to create another index for the date field, would this be a dilemma optimization indexes put the insert query, update, web visits are recorded when the user visits the frontend of the website slow. – Learning and sharing Sep 01 '16 at 22:03
  • In all cases you can use index range filtering. You just have to compute the start and end `DATETIME` you want, and then use `WHERE` clauses like in my answer. There's probably no need for extra columns with rounded-off `DATETIME` values. – O. Jones Sep 01 '16 at 22:05
  • Perfect example of such a query, WHERE DATE_FORMAT(create_at, '%Y-%m-%d') = CURDATE(), It can be slow, so understood in its response, WHERE MONTH(create_at) = MONTH(NOW(), that 2 functions are being used to make the calculation to all records, if there are millions slow so if that would make the query, but using DATE_FORMAT(), can also affect query performance? – Learning and sharing Sep 01 '16 at 22:13
  • Read about sargable queries. **Any function** applied to a column in a `WHERE` clause makes the query nonsargable. – O. Jones Sep 01 '16 at 22:16
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/122458/discussion-between-learning-and-sharing-and-ollie-jones). – Learning and sharing Sep 01 '16 at 22:20