List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 14 1999 8:12pm
Subject:Re: Query Help
View as plain text  
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


Query HelpJay Miller13 Apr
  • Re: Query HelpDan Nelson13 Apr
  • Re: Query HelpFred Read13 Apr
  • Re: Query HelpChristian Mack15 Apr