Blog > MySQL/MariaDB Query for Counting API Requests per Day

MySQL/MariaDB Query for Counting API Requests per Day

Published on Saturday, February 8, 2020 by Lisa Schleifer

---

---

When we started our holidays API project, we only had a select number of users using our closed beta, we needed a quick and easy way to track API metrics. The first metric we wanted to track was obviously, the number of requests, the beta API was receiving.

In this article, we are going to describe, how we set this us very quickly and started getting a quick view of what was going on with the API. This exercise was actually the initial step to building a full-fledged dashboard and monitoring system around our API.

Firstly, let create the schema for the table that will be tracking the hits. This is a basic table that just has the fields id, created_date, user_id, and ip. For every request that comes in, we want to insert a new row into the database. For the purposes of this demo, this will work, however in a real-time production system this system will not scale.

Below is the schema to create the table.

CREATE TABLE `api_hits` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` int(11) DEFAULT NULL,
  `ip` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=51647 DEFAULT CHARSET=utf8mb4;

With the table created, let’s insert some dummy records into the table to make it easy to see results when we perform queries on this table. Typically, your application will perform this insert when an API request comes in. We are only inserting this dummy data here for the purpose of this tutorial

INSERT INTO api_hits
    (user_id, ip)
VALUES
    ('3a561110-6369-11e9-a923-1681be663d3e', '191.23.2.3'),
    ('3a561110-6369-56e9-a923-1681be663d3e', '193.23.2.3'),
    ('3a561110-6333-11e9-a923-1681be663d3e', '193.23.2.3'),
    ('3a561110-6311-11e9-a923-1681be663d3e', '191.23.2.3'),
    ('3a561110-4369-11e9-a923-1681be663d3e', '191.23.2.3'),
    ('3a561110-5369-11e9-a923-1681be663d3e', '191.23.2.3'),
    ('3a561110-6239-11e9-a923-1681be663d3e', '181.13.2.3');

You will notice that the table has 4 columns but we are only inserting into 2 of them, the other two columns are the auto-increment field and the date fields which are auto-generated. Some people might argue that the id is not required because we might not use it for anything but it has a use case.

Next, let’s use the MySQL date functions to select all the request that come in per day. You would notice from the query below that we are joining the day, year and month into a day field and then counting every request that happened on that day by grouping the request by that same field.

SELECT 
  CONCAT(
    MONTHNAME(created_date), 
    '-', 
    DAY(created_date), 
    '-', 
    YEAR(created_date)
  ) as day_date, 
  COUNT(created_date) AS hits 
FROM 
  api_hits 
GROUP BY 
  day_date 
ORDER BY 
  day_date DESC;

Once you have successfully run the query, you should see a response like which includes the day and the number of API hits

Now, let’s create a view that shows us all the hits we’ve had per day. The view just makes it easy to get the same results without rewriting the query every day. The first part of the query just builds out the day by using the built-in MySQL date functions such as MONTHNAME, DAY and YEAR

CREATE VIEW v_api_hits_per_day as 
SELECT 
  CONCAT(
    MONTHNAME(created_date), 
    '-', 
    DAY(created_date), 
    '-', 
    YEAR(created_date)
  ) as day_date, 
  COUNT(created_date) AS hits 
FROM 
  api_hits 
GROUP BY 
  day_date 
ORDER BY 
  day_date DESC;

With the view created, you should be able to do a select on the view to view the results in real-time. We hope you enjoyed this article or at least found it helpful, here at Calendarific, provide an API that returns bank, public holidays and observances for 200 countries with their associated regions and states.