List:General Discussion« Previous MessageNext Message »
From:Fred Lindberg Date:March 21 1999 8:00pm
Subject:Re: how to optimize
View as plain text  
On Sun, 21 Mar 1999 11:16:47 +0200, kalle volkov wrote:

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

1. Rearrange HOST=d1.d2.d3 => TSOH=d3.d2.d1.
2. Make index (TSOH)
3. retrieve by LIKE 'ee.%'

Alternatively, Parse d1.d2.d3...dn to retrieve dn. Store dn separately
and index it.

SELECT ... GROUP BY dn ORDER BY hownany ...

Depends on what you want to do with the data, but either solution
should return your results more of less instantaneously.

-Sincerely, Fred

(Frederik Lindberg, Infectious Diseases, WashU, St. Louis, MO, USA)


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