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 | &lt;derived2&gt; | 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   |
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.