CREATE TABLE `stats` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` bigint(20) unsigned NOT NULL COMMENT 'UID',
  `title` varchar(255) DEFAULT NULL COMMENT 'Заголовок',
  `url` text NOT NULL COMMENT 'Урл',
  `domain` varchar(255) NOT NULL COMMENT 'домен',
  `domain_id` int(10) DEFAULT NULL COMMENT 'ID домена',
  `referrer` text COMMENT 'Реферер',
  `domain_ref` varchar(255) NOT NULL COMMENT 'домен',
  `domain_ref_id` int(10) DEFAULT NULL COMMENT 'ID домена рефера',
  `ip` bigint(20) NOT NULL COMMENT 'IP-адрес',
  `browser` varchar(255) DEFAULT NULL COMMENT 'Браузер',
  `lang` varchar(100) DEFAULT NULL COMMENT 'Язык браузера',
  `created_at` datetime DEFAULT NULL COMMENT 'Дата',
  PRIMARY KEY (`id`),
  KEY `fk_stats_uid_idx` (`uid`),
  KEY `fk_stats_ip_idx` (`ip`),
  KEY `fk_stats_domain_ref_idx` (`domain_ref`),
  KEY `fk_stats_domain_idx` (`domain`),
  KEY `fk_stats_domain_id_index` (`domain_id`),
  KEY `fk_stats_domain_ref_id_index` (`domain_ref_id`)
) ENGINE=InnoDB AUTO_INCREMENT=271568 DEFAULT CHARSET=utf8

SELECT 
    COUNT(*)
FROM
    (SELECT 
        `stats`.`uid`,
            COUNT(uid) AS `count`,
            COUNT(DISTINCT domain_id) AS `count_domains`
    FROM
        `stats`
    WHERE
        (`stats`.`uid` NOT IN (9421802727 , 5487722167, 4473237845, 7226083698, 2238407807, 4771231133, 3568163855, 7567680618, 9039386283, 1980237969))
            AND (`stats`.`ip` NOT IN (3579284812 , 3003068090, 620875042, 3322362487))
    GROUP BY `stats`.`uid`) `c`

+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+------------------------------+
| id | select_type | table | type  | possible_keys                    | key              | key_len | ref  | rows   | Extra                        |
+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+------------------------------+
|  1 | PRIMARY     | NULL  | NULL  | NULL                             | NULL             | NULL    | NULL |   NULL | Select tables optimized away |
|  2 | DERIVED     | stats | index | fk_stats_uid_idx,fk_stats_ip_idx | fk_stats_uid_idx | 8       | NULL | 235101 | Using where                  |
+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+------------------------------+

return sprintf('%u', ip2long(trim($ip)));

CREATE TABLE stats
(
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    uid BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255),
    url LONGTEXT NOT NULL,
    domain VARCHAR(255) NOT NULL,
    domain_id INT,
    referrer LONGTEXT,
    domain_ref VARCHAR(255) NOT NULL,
    domain_ref_id INT,
    ip BIGINT NOT NULL,
    browser VARCHAR(255),
    lang VARCHAR(100),
    created_at DATETIME
);
CREATE INDEX fk_stats_domain_id_index ON stats (domain_id);
CREATE INDEX fk_stats_domain_idx ON stats (domain);
CREATE INDEX fk_stats_domain_ref_id_index ON stats (domain_ref_id);
CREATE INDEX fk_stats_domain_ref_idx ON stats (domain_ref);
CREATE INDEX fk_stats_ip_idx ON stats (ip);
CREATE INDEX fk_stats_uid_idx ON stats (uid);
CREATE INDEX fk_stats_uid_to_ip_idx ON stats (uid, ip);

+----+-------------+-------+-------+---------------------------------------------------------+------------------+---------+------+--------+------------------------------+
| id | select_type | table | type  | possible_keys                                           | key              | key_len | ref  | rows   | Extra                        |
+----+-------------+-------+-------+---------------------------------------------------------+------------------+---------+------+--------+------------------------------+
|  1 | PRIMARY     | NULL  | NULL  | NULL                                                    | NULL             | NULL    | NULL |   NULL | Select tables optimized away |
|  2 | DERIVED     | stats | index | fk_stats_uid_idx,fk_stats_ip_idx,fk_stats_uid_to_ip_idx | fk_stats_uid_idx | 8       | NULL | 240855 | Using where                  |
+----+-------------+-------+-------+---------------------------------------------------------+------------------+---------+------+--------+------------------------------+

SELECT 
    `stats`.`uid`,
    COUNT(uid) AS `count`,
    COUNT(DISTINCT domain_id) AS `count_domains`
FROM
    `stats`
WHERE
    (`stats`.`uid` NOT IN (9421802727 , 5487722167,
        4473237845,
        7226083698,
        2238407807,
        4771231133,
        3568163855,
        7567680618,
        9039386283,
        1980237969))
        AND (`stats`.`ip` NOT IN (3579284812 , 3003068090, 620875042, 3322362487))
GROUP BY `stats`.`uid`
ORDER BY `count` DESC
LIMIT 40

+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type  | possible_keys                    | key              | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | stats | index | fk_stats_uid_idx,fk_stats_ip_idx | fk_stats_uid_idx | 8       | NULL | 239517 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+----------------------------------+------------------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)