In the last episode (Apr 13), Jay Miller said:
> 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.
Can you strip these leading zeros out _before_ inserting into the
table, and/or split the username and realm into different fields?
What you might consider doing is breaking this information out into a
couple of other tables. One would be a "realms" table:
realmid int NOT NULL AUTO_INCREMENT
realmname varchar(20) NOT NULL
and the other would be a "users" table:
userid int NOT NULL AUTO_INCREMENT
username varchar(20) NOT NULL
realm int
users.realmid is a pointer back into the "realms" table. Both tables
should have indexes on all fields. So instead of inserting a username
into your call table, do a lookup into the "users" table, and insert
the userID instead.
If you want a list of all the calls from a particular person,
select start_time, end_time
from usertime, users, realms
where usertime.user=users.userid and users.realm=realms.realmid and
users.username="myusername" and realms.realmname="myrealm.com";
If you want a count of calls per realm,
select realmname, count(*) from usertime,users,realms where
usertime.user=users.userid and users.realm=realms.realmid group by
realmid;
-Dan Nelson
dnelson@stripped
