hi!
problem: i want to make
---
SELECT SUBSTRING_INDEX(ip,'.',-1) AS temp, count(ip) AS howmany FROM
MyDatabase GROUP BY temp ORDER BY howmany DESC LIMIT 10;
---
but it takes SOOOOO long (4 min)... is there a possibility to make it
faster :?
and now for the machine and database:
---
machine is: P2-350/128 , HDD is IBM UW SCSI 18G (shouldn't be a
problem), swap is 128M
redhat 5.2, latest MySQL from source RPM...
---
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24187 to server version: 3.22.20a
Type 'help' for help.
mysql> show fields from MyDatabase;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| ID | text | YES | | NULL | |
| date | datetime | YES | | NULL | |
| ip | text | YES | | NULL | |
| browser | text | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select count(ip) from MyDatabase;
+-----------+
| count(ip) |
+-----------+
| 360285 |
+-----------+
1 row in set (2.25 sec)
mysql> select SUBSTRING_INDEX(ip,'.',-1) AS temp, count(ip) AS howmany
FROM MyDatabase GROUP BY temp ORDER BY howmany DESC LIMIT 10;
+------+---------+
| temp | howmany |
+------+---------+
| ee | 171518 |
| com | 41635 |
| net | 37725 |
| jp | 7985 |
| de | 7662 |
| fi | 4573 |
| ca | 3291 |
| uk | 2742 |
| se | 2729 |
| edu | 2698 |
+------+---------+
10 rows in set (4 min 42.96 sec)
mysql> exit
mysql> exit
Bye
[me@kiisu mysql]# uptime
11:18am up 21:21, 3 users, load average: 2.73, 1.40, 0.72
[me@kiisu mysql]# free
total used free shared buffers
cached
Mem: 127716 65656 62060 84772 2164
31748
-/+ buffers/cache: 31744 95972
Swap: 128484 5144 123340
[me@kiisu mysql]#
---
