Mysql – SQL Limit + count group

countlimitsMySQLorder-by

I'm tracking all the visitors of my site using this table:

In my admin panel, I have an overview of which countries visited my site, where they came from, …, but because over 20+ different countries have visited my site already, the chart looks like this:

Messed up chart

To solve this, I want to select the top 5 countries, but I have no clue on how to do this.

I've tried using LIMIT & COUNT but no luck.

Best Answer

You can separate the problem into multiple steps.

First lets create an example table:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Visitors(
  id INT,
  -- other columns
  country CHAR(2)
);

INSERT INTO Visitors(id,country)VALUES(1,'DE');
INSERT INTO Visitors(id,country)VALUES(2,'DE');
INSERT INTO Visitors(id,country)VALUES(3,'DE');
INSERT INTO Visitors(id,country)VALUES(4,'BE');
INSERT INTO Visitors(id,country)VALUES(5,'BE');
INSERT INTO Visitors(id,country)VALUES(6,'BE');
INSERT INTO Visitors(id,country)VALUES(7,'BE');
INSERT INTO Visitors(id,country)VALUES(8,'BE');
INSERT INTO Visitors(id,country)VALUES(9,'BE');
INSERT INTO Visitors(id,country)VALUES(10,'US');
INSERT INTO Visitors(id,country)VALUES(11,'US');
INSERT INTO Visitors(id,country)VALUES(12,'US');
INSERT INTO Visitors(id,country)VALUES(13,'US');
INSERT INTO Visitors(id,country)VALUES(14,'HR');
INSERT INTO Visitors(id,country)VALUES(15,'HR');
INSERT INTO Visitors(id,country)VALUES(16,'SE');
INSERT INTO Visitors(id,country)VALUES(17,'SE');
INSERT INTO Visitors(id,country)VALUES(18,'SE');
INSERT INTO Visitors(id,country)VALUES(19,'SE');
INSERT INTO Visitors(id,country)VALUES(20,'RU');

The first step now is to count the impressions per country using the GROUP BY clause:

Query 1:

SELECT country,COUNT(1) cnt
FROM Visitors
GROUP BY country

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      DE |   3 |
|      HR |   2 |
|      RU |   1 |
|      SE |   4 |
|      US |   4 |

From there we can get the top 5 countries using LIMIT:

Query 2:

SELECT *
FROM(
  SELECT country,COUNT(1) cnt
  FROM Visitors
  GROUP BY country
)V
ORDER BY cnt DESC
LIMIT 5

Results:

| COUNTRY | CNT |
-----------------
|      BE |   6 |
|      SE |   4 |
|      US |   4 |
|      DE |   3 |
|      HR |   2 |

Now we just need to join the result back to the visitors table:

Query 3:

SELECT V.*,c.cnt 
FROM Visitors V
JOIN(
  SELECT *  
  FROM(
    SELECT country,COUNT(1) cnt
    FROM Visitors
    GROUP BY country
  )V
  ORDER BY cnt DESC
  LIMIT 5
)C
ON V.country = C.country

Results:

| ID | COUNTRY | CNT |
----------------------
|  1 |      DE |   3 |
|  2 |      DE |   3 |
|  3 |      DE |   3 |
|  4 |      BE |   6 |
|  5 |      BE |   6 |
|  6 |      BE |   6 |
|  7 |      BE |   6 |
|  8 |      BE |   6 |
|  9 |      BE |   6 |
| 10 |      US |   4 |
| 11 |      US |   4 |
| 12 |      US |   4 |
| 13 |      US |   4 |
| 14 |      HR |   2 |
| 15 |      HR |   2 |
| 16 |      SE |   4 |
| 17 |      SE |   4 |
| 18 |      SE |   4 |
| 19 |      SE |   4 |

Related Solutions

Mysql – COUNT and GROUP BY performance

Short answer: No.

Detailed answer: Unless you add/already have an index there that covers the country column the database engine has no way of telling the total number of instances to count. Even if you limit the output the entire table has to be scanned to retrieve every instance first. Of course, since this is a logging table, with potentially a large dataset, I'd avoid adding indexes to the table so:

not to increase INSERT time
to minimize space/memory usage the indexes would otherwise require

Solution: create a second table that will keep a running total for all countries, that gets updated by a trigger on the hits_table or directly by the application that inserts the data into hits_table. Something in the lines of:

CREATE TABLE `country_hits_totals` (
  `country` VARCHAR(255),
  `date`    DATE,
  `visits_total` BIGINT
);

This would make the table very small and allow you to create a PRIMARY KEY on the country and date columns, dedicating minimum resources. Once that key is set up, you could employ a trivial upsert statement (see this SQL Fiddle session for a full test case with a trigger-based usage):

INSERT INTO `country_hits_totals` (
  `country`,
  `date`,
  `visits_total`
)
SELECT
  NEW.country,
  NEW.timestamp,
  1
ON DUPLICATE KEY UPDATE `visits_total` = `visits_total` + 1;

Query blocking note: In a heavy traffic scenario you ideally want to query country_hits_totals with non-blocking statements only. Otherwise the upsert on the trigger gets delayed, and by extensions entire transaction that kicked it off. In a perfect world country_hits_totals would get copied over to a data warehouse at the time of least daily traffic, and you'd run your BI queries on that warehouse. That said, this is large traffic/enterprise territory and possible solutions depend on how quickly you want to get the data. Say, if you need an update only once a day you're better off pre-aggregating data onto country_hits_totals on a schedule instead of relying on a trigger updating the rows live.

Mysql – Speed up COUNT(*) when using an index

The query will be slow because cardinality of category index is low. There are 12 categories, so in average the query will read 1/12 part of the index. You can't improve this query.

Your original approach can improve overall performance. Just instead of manually updating book_count create a trigger on INSERT and DELETE event.

UPDATE: To prove the query will partially read index category

mysql> select count(*) from books;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select category, count(*) from books group by 1;
+----------+----------+
| category | count(*) |
+----------+----------+
|        0 |       50 |
|        1 |       77 |
|        2 |       88 |
|        3 |       84 |
|        4 |      102 |
|        5 |       79 |
|        6 |       79 |
|        7 |       73 |
|        8 |       84 |
|        9 |       76 |
|       10 |       87 |
|       11 |       83 |
|       12 |       38 |
+----------+----------+
13 rows in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from books where category = 6;
+----------+
| count(*) |
+----------+
|       79 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'Hand%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 79    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)

Best Answer

Related Solutions

Mysql – COUNT and GROUP BY performance

Mysql – Speed up COUNT(*) when using an index

Related Question