List:General Discussion« Previous MessageNext Message »
From:Jay Miller Date:April 13 1999 3:09pm
Subject:Query Help
View as plain text  
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

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