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
SOCKET Internet Services
Columbia, MO, US
