List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 21 1999 9:37pm
Subject:how to optimize
View as plain text  
>>>>> "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

Thread
how to optimizekalle volkov21 Mar
  • Re: how to optimizeAyman Haidar21 Mar
    • Re: how to optimizeThimble Smith21 Mar
  • how to optimizeMichael Widenius21 Mar
Re: how to optimizeFred Lindberg21 Mar