From: kalle volkov
Date: March 21 1999 9:16am
Subject: how to optimize
List-Archive: http://lists.mysql.com/mysql/709
Message-Id: <36F4B8FF.777B@tank.ee>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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]# 
---