There is a table:

CREATE TABLE `log_site` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT, `date` DATETIME NOT NULL, `ip` VARCHAR(20) NOT NULL, `agent` TEXT NOT NULL, `http_referer` TEXT NOT NULL, `request_url` TEXT NOT NULL, `method` VARCHAR(10) NOT NULL, PRIMARY KEY (`id`))COLLATE='utf8_general_ci'ENGINE=MyISAM AUTO_INCREMENT=2503081;

200 thousand lines

SELECT ip, (SELECT COUNT(*) FROM log_site AS t2 WHERE t2.ip = t1.ip) AS count_data FROM log_site AS t1 GROUP BY ip query SELECT ip, (SELECT COUNT(*) FROM log_site AS t2 WHERE t2.ip = t1.ip) AS count_data FROM log_site AS t1 GROUP BY ip executed 6 hours

Smashed into requests for functions in Python, completed in 20 minutes.

enter image description here

The settings in my.conf are default.

    1 answer 1

     SELECT COUNT(*) as cnt,ip FROM log_site GROUP BY ip

    for the ip field, use the index and change the field type from VARCHAR to INT (10) Unsigned. For conversion, use the INET_ATON and INET_NTOA functions.

    • Thank you your fast 0.3 sec. But why does mine work so long, I understand that it should work longer than you, but why is it 6 hours, is it really so hard? - Den Kapone
    • Your request is incorrect, in fact double work. read how GROUP BY - sterx works
    • 0.3 seconds is also a lot. We need an index and type conversion - sterx