Thus spake kalle volkov (kalle@stripped):
> 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]#
> ---
>
> ---------------------------------------------------------------------
> To request this thread, e-mail mysql-thread709@stripped
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
> For archive commands, etc, e-mail: mysql-help@stripped
>
I am no expert, but shouldn't you use some indexes, may be on ip.
--
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
| Ayman Haidar |
| haidar@stripped |
| just another linux and vi lover. |
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
