From: Thimble Smith
Date: March 21 1999 7:40pm
Subject: Re: how to optimize
List-Archive: http://lists.mysql.com/mysql/733
Message-Id: <19990321124029.G25614@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Sun, Mar 21, 1999 at 11:25:38AM -0500, Ayman Haidar wrote:
> Thus spake kalle volkov (kalle@stripped):
> > SELECT SUBSTRING_INDEX(ip,'.',-1) AS temp, count(ip) AS howmany FROM
> > MyDatabase GROUP BY temp ORDER BY howmany DESC LIMIT 10;
>
>  I am no expert, but shouldn't you use some indexes, may be on ip.

The problem is that you can't use an index on ip, because the
SUBSTRING_INDEX(...) function still has to be called for every
row in the table.  I suppose it would be slightly better (i.e.,
MySQL could use the index file instead of a full table search)
but you'd still have to go through every single row.

Tim