List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 13 1999 3:27pm
Subject:Re: Query Help
View as plain text  
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
Thread
Query HelpJay Miller13 Apr
  • Re: Query HelpDan Nelson13 Apr
  • Re: Query HelpFred Read13 Apr
  • Re: Query HelpChristian Mack15 Apr