>>>>> "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
