From: Christian Mack
Date: April 14 1999 8:12pm
Subject: Re: Query Help
List-Archive: http://lists.mysql.com/mysql/1852
Message-Id: <3714F6A1.2C89A4BE@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Jay Miller wrote:
> 
> I am trapped in Way-Too-Slow-Query land and am looking for help.
> 
> I have a table of user call information.  I need to extract one record from
> the table for each of a certain class of customer from a certain range of
> times.  So, for instance, all users whose name ends with '@realm', during
> the month of February.
> 
> This is complicated by the fact that the names can have a variable number of
> leading 0's, so:
> 
> 000123@realm
> 00123@realm
> 123@realm
> 
> Are all the same name.
> 
> The only way I can figure out how to do this is to first extract all of the
> names that match, then query for each name in the list with a LIMIT 1.  So:
> 
> mysql> SELECT TRIM(LEADING '0' FROM name) As Login INTO OUTFILE 'FebRealm'
> FROM usertime WHERE name LIKE '%@realm' AND start_time BETWEEN '1999-02-01
> 00:00:00' And '1999-03-01 00:00:00' GROUP BY Login;
> 
> Followed by a shell script:
> 
> #!/bin/csh
> foreach name (`cat FebRealm`)
>         echo "SELECT * FROM usertime WHERE name Like '%$name' AND start_time
> BETWEEN '1999-02-01 00:00:00' And '1999-03-01 00:00:00' | mysql -s database
> >> FebRealmOneRec
> end
> 
> This solution works but:
> 1) Is looks to me like a terribly inelegant hack.
> 2) The table in question has ~1.1 million records, and there are about 6000
> names to be searched.  Since the name field search has a wildcard for the
> first character, the query can't use an index on name, so this query takes
> about 5 seconds to run.  Doing that once for each of 6000 names is 5 * 6000
> = 300,000 sec = 8.3 hours!
> 
> I am hoping that the proper application of indexes and querying techniques
> can speed this operation up.  I have two different searches like this I am
> supposed to do today.
> 
> Thanks,
> Jay Miller

Hi Jay

Excuse if I'm totally wrong, but are you looking for this?
SELECT *, name+0 AS Login FROM usertime WHERE name LIKE '%@realm' AND start_time BETWEEN '1999-02-01 00:00:00' AND '1999-03-01 00:00:00' ORDER BY Login

Tschau
Christian