From: Michael Widenius
Date: March 21 1999 9:37pm
Subject: how to optimize
List-Archive: http://lists.mysql.com/mysql/744
Message-Id: <14069.25850.789265.403318@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "kalle" == kalle volkov <kalle@stripped> writes:

kalle> hi!
kalle> problem: i want to make 
kalle> ---
kalle> SELECT SUBSTRING_INDEX(ip,'.',-1) AS temp, count(ip) AS howmany FROM
kalle> MyDatabase GROUP BY temp ORDER BY howmany DESC LIMIT 10;
kalle> ---
kalle> but it takes SOOOOO long (4 min)... is there a possibility to make it
kalle> faster :?

kalle> and now for the machine and database:

kalle> ---
kalle> machine is: P2-350/128 , HDD is IBM UW SCSI 18G (shouldn't be a
kalle> problem), swap is 128M

Note that in cases with ORDER BY and many rows, the problem is the
number of seeks that must be made;  In the above case, MYSQL does:

- Sort the table based on the GROUP BY column
- Write an entry in a temporary table for each group.
- Sort the whole temporary table based on howmany
- output the 10 best rows.

In MySQL 3.22.20, you can get the above a bit faster by using:

SELECT SQL_SMALL_RESULT SUBSTRING_INDEX(ip,'.',-1) AS temp, count(ip)
AS howmany FROM MyDatabase GROUP BY temp ORDER BY howmany DESC LIMIT 10

This fill force MySQL to skip the GROUP BY and instead use a
in memory temporary table with a key on 'temp'.

I don't know how you can do the above query faster than that...

Regards,
Monty