# Determine number of times an id was used with a domain IP

This is a slow query and the table has 50 million rows.

Table named 'log':

| id     | domainIP        |
| foo    | 158.132.34.5    |
| bob    | 128.12.244.3    |
| bob    | 128.12.244.3    |
| bob    | 19.152.134.4    |
| bob    | 168.152.34.9    |
| alice  | 178.132.64.10   |
| alice  | 188.152.214.200 |
| peter  | 208.162.36.153  |
| peter  | 208.162.36.153  |
| peter  | 208.162.36.153  |
| peter  | 198.168.94.201  |


The following query, to get the number of times id was used with each 'domainIP', and the percentage of each:

SELECT
log.id,
log.domainIP,
COUNT(log.domainIP) AS "Times",
totalsTable.Totals,
(COUNT(log.domainIP)/totalsTable.Totals)*100 AS "Percentage"
FROM log
JOIN
(
SELECT
id,
COUNT(domainIP) AS Totals
FROM log GROUP BY id
) AS totalsTable

ON (log.id = totalsTable.id)

GROUP BY log.domainIP ORDER BY log.id ASC, "Percentage"  DESC


It returns:

| id     | domainIP        | Times | Totals | Percentage
| foo    | 158.132.34.5    | 1     | 1      | 100
| bob    | 128.12.244.3    | 2     | 4      | 50
| bob    | 19.152.134.4    | 1     | 4      | 25
| bob    | 168.152.34.9    | 1     | 4      | 25
| alice  | 178.132.64.10   | 1     | 2      | 50
| alice  | 188.152.214.200 | 1     | 2      | 50
| peter  | 208.162.36.153  | 3     | 4      | 75
| peter  | 198.168.94.201  | 1     | 4      | 25


It returns the result I need, but it's unusable slow.

EDIT: Here's the table structure exported from phpmyadmin.

CREATE TABLE log (
id varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
eDate datetime DEFAULT NULL,
domainIP varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
event varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE log
ADD UNIQUE KEY logUnique (id,eDate,event),
ADD KEY eDate (eDate),
ADD KEY id (id,eDate),
ADD KEY event (id,eDate,event);


Results of EXPLAIN query:

id | select_type | table | type  | possible_keys      | key       | key_len | ref            | rows  | Extra
1 | PRIMARY | <derived2> | ALL   | NULL               | NULL      | NULL    | NULL           | 100   | Using where; Using temporary; Using filesort
1 | PRIMARY | log        | ref   | logUnique,id,event | logUnique | 453     | totalsTable.id | 1     |
2 | DERIVED | log        | index | NULL               | id        | 459     | NULL           | 100   |

• Put an index on domainIP Jul 25 '17 at 14:02
• @Paparazzi i added the index on a duplicate table with a lot less data, it decreased the query time in 20% +/-. It's an improvement but doesn't really help, when i added it to the real size db it performed as usual (meaning i waited several minutes and it still didn't return the result) Jul 25 '17 at 14:24
• @Paparazzi An index on domainIP would not help this query, as domainIP is just being counted, not used as an aggregation dimension, join condition, filtering condition, sort criteria, etc. Now if OP decides to aggregate on domainIP (something that potentially makes sense), then an index would be needed. Jul 27 '17 at 18:12
• As you might have heard there is also NoSql, basically in this scenario where you need to have a large sum of data, and as i see(might be I'm wrong) you don't extend this log table anywhere else, which means that NoSql could help you out more here than MySql.stackoverflow.com/a/10554235/7940698 Aug 8 '17 at 7:54
• Try a composite/compound index on (id, domainIP). That should remove the "filesort" from the execution plan. Aug 29 '17 at 20:20

First, I would ask if you really need the percentage calculation to happen in the query result set (vs. being able to calculate this in application layer). This is really what is causing complexity in your use case, as being able to get row-level data and summary data (the percentage) in a single query is a bit of a challenge in most relational databases.

If you don't require this in your actual query, then you can greatly simplify this query to something like:

SELECT
id,
domainIP,
COUNT(domainIP) AS times
FROM log
GROUP BY id, domainIP
ORDER BY id ASC, times DESC


This would eliminate your subselect and the resulting filesort that is happening because of it. Of course, the query above would require an index on domainIP to be optimized.

This would give a result set like:

| id     | domainIP        | times
| foo    | 158.132.34.5    | 1
| bob    | 128.12.244.3    | 2
| bob    | 19.152.134.4    | 1
| bob    | 168.152.34.9    | 1
| alice  | 178.132.64.10   | 1
| alice  | 188.152.214.200 | 1
| peter  | 208.162.36.153  | 3
| peter  | 198.168.94.201  | 1


This may or may not be trivial to compile this into your percentage results in your application layer depending on how many unique id values you have.

You could potentially make it even easier by using WITH ROLLUP clause in your GROUP BY (see https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html for more info).

That query might look like this:

SELECT
id,
domainIP,
COUNT(domainIP) AS times
FROM log
GROUP BY id ASC, domainIP ASC WITH ROLLUP


This would yield a result set like:

| id     | domainIP        | times
| foo    | 158.132.34.5    | 1
| foo    | NULL            | 1
| bob    | 128.12.244.3    | 2
| bob    | 19.152.134.4    | 1
| bob    | 168.152.34.9    | 1
| bob    | NULL            | 4
| alice  | 178.132.64.10   | 1
| alice  | 188.152.214.200 | 1
| alice  | NULL            | 2
| peter  | 208.162.36.153  | 3
| peter  | 198.168.94.201  | 1
| peter  | NULL            | 4
| NULL   | NULL            | 11


Note here that there are added rows where the NULL values represent case where you have subtotal for the preceding aggregation set (i.e. for each of you id values). This could make it even easier for application logic to pick up you group-level totals.

Now, if you truly need to get percentages totals in the result set, then I would first take a step back and really understand what the use case is. Do you really need to make this query on an ad hoc basis with a need for exact real-time results? If so, you may just have to live with the fact that your query may take a long time to run, as you will always have to calculate the totals as aggregated at id level and at id/domainIP level.

If however you can live with periodically updating the aggregations (like scheduled via cron, or simlar), then your options open up greatly to deliver better performance. Perhaps you runs a query like I have shown above and place the results into a temporary table every X minutes and then use that table as the basis for your query. Perhaps you look at wholly separate summary tables (as suggested in the answer by @Siegen), with trigger-based updates. Perhaps you have a wholly separate ETL process which takes this data and populates in into a data store more geared towards data reporting (i.e. a data warehouse). Whatever the case may be, I would suggest that you might need to take a step back and think about what you use case really requires, and my guess is that you will find it does not require ad-hoc, real-time, query results like what you are trying to get here.

With regards to your table schema:

• Your id and event index are totally unnecessary and are likely hurting insert performance on this table. event is an exact duplicate of logUnique (just withut the uniqueness constraint, which makes no sense). id index use cases can be satisfied via the logUnique index as well, as the first two fields in the index are exactly the same and in the same order.
• You might consider doing away with camelCasing of any database object identifiers (table names, field names, etc.) as case-sensitivity is typically quite meaningless in MySQL and may lead to some problems when applied across different operating systems.. Many MySQL admin folks consider it best practice to simply use snake_case for naming all database object identifiers to keep things simple. Read https://dev.mysql.com/doc/refman/5.7/en/identifier-case-sensitivity.html for more on this.
• Well, the percentage calculation is not a bit of a challenge in most relational databases, only in those which dont't support Windowed Aggregates, and that's a minority :-) Then it's a simple 100*COUNT(domainIP)/SUM(COUNT(domainIP)) Jul 28 '17 at 8:52

Assuming you have the proper indexes set up on all of these columns and it's still too slow, you might consider storing the data in the database and updating it on demand.

This is an especially fine case for storing the counts and percentages, since the data can never substantively change. Once someone hits the service from an IP, that is simply historical fact.

So you could change the schema to be

| id | domainIP | ipTimesSeen | idTimesSeen | percentOfIdTimesSeen |


Then, increment the ipTimesSeen, idTimesSeen, and update percentofIdTimesSeen when the user hits the service.

You're duplicating data for the counts but it's presumably pretty small data, anyways. And it's the age-old tradeoff between cycles and memory.

Alternatively, if you have a users table, you might store the idTimesSeen column there, as that's where it'd really belong. As well, "percent of total" might be okay to recalculate with each query (would need to be tested).

Users

| id | timesSeen |


Log

| id | domainIP | timesSeen |


In either case, this is the strategy a lot of sites use. Recounting rows can be quite burdensome if you have to do it frequently. It can be worth the extra complexity to store relationship counts at the time that those relationships change, if you're saving the database from having to dump the query to disk.

• Increasing the count is fine (and might save lots of disk space), but not recalculating percentage. This would result in reading/updating all rows of a user for each access. Jul 25 '17 at 6:29
• Yeah, it would depend on the actual distribution of his data and traffic patterns. If a given user is only ever represented by a few IPs and rarely vists, then properly indexed columns would mean recalculating the percentage would be trivial, even for each visit. But that's why I left it out of my latter example. Jul 25 '17 at 15:34
• Whereas I generally agree with this, cycles vs. memory is not the only tradeoff. You're also sacrificing a guarantee of correctness / whether the data are up-to-date. If there's corruption or a burp in the code that updates the seen column, you've forever lost the count. Jul 25 '17 at 16:27
• True. I guess it's up to his tolerance for data accuracy. I generally assume that IP log files are pretty disposable but I maybe shouldn't assume that for his use case. Jul 25 '17 at 16:51
• @Reinderien: When there's corruption or a burp in the code that inserts a new row in the original log table, you've forever lost the count :-) Jul 25 '17 at 17:54