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
