From: Ayman Haidar
Date: March 21 1999 4:25pm
Subject: Re: how to optimize
List-Archive: http://lists.mysql.com/mysql/712
Message-Id: <19990321112538.A13020@usol.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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.			|
 -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-