Nested / join syntax

carazuul, I tried your suggestion. Still no go. I've let the query process for 10 min without finishing.
Might it just be the size of the table? To be honest, I've never worked with tables more than 15,000 rows or so before. The server should be plenty powerful enough - it's got 4 gigs of RAM and two dual core 3GHz processors.

Here's the create table results:

CREATE TABLE `firewall_traffic` (
  `host` varchar(32) default NULL,
  `date` date default NULL,
  `time` time default NULL,
  `src` varchar(20) default NULL,
  `dst` varchar(20) default NULL,
  `dstname` varchar(50) default NULL,
  `msg` text,
  `seq` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`seq`),
  KEY `host` (`host`),
  KEY `date` (`date`,`time`),
  KEY `src` (`src`,`dst`),
  KEY `dstname` (`dstname`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `user_activity` (
  `host` varchar(32) default NULL,
  `date` date default NULL,
  `time` time default NULL,
  `evtlog` varchar(32) default NULL,
  `evtid` int(11) default NULL,
  `source` varchar(32) default NULL,
  `user` varchar(50) default NULL,
  `type` varchar(32) default NULL,
  `category` varchar(32) default NULL,
  `msg` text,
  `addr` varchar(20) default NULL,
  `seq` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`seq`),
  KEY `host` (`host`),
  KEY `date` (`date`,`time`),
  KEY `evtlog` (`evtlog`),
  KEY `evtid` (`evtid`),
  KEY `source` (`source`),
  KEY `type` (`type`),
  KEY `category` (`category`),
  KEY `user` (`user`),
  KEY `addr` (`addr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And my.cnf:
[mysqld]
datadir=/data
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

FrivolousSam:
The differences in the not queries were just copy and paste errors - I had tried about 20 different variations when I posted my question, and I just selected the wrong one.
I've tried executing the query provided, including the limit clause. The only thing I changed was the date line to firewall_traffic.date='2008-07-16' (I was getting ambiguity errors), plus activating the limit line.

The speed problem persists. With a limit 1, the query took over 5 minutes to execute. That's definitely better, but it's still longer than I'd expect.  

I've been thinking about your comment about the indexes. All the columns you mentioned are indexed, but a few have multiple elements in the their indexes; firewall traffic `date` (`date`,`time`),  `src` (`src`,`dst`, and user `date` (`date`,`time`), to be precise. Do multi part indexes hurt anything here?

I've found a working solution to this problem, albeit not quite what I was expecting.
I went back to thinking about the pure volume of data being joined. Since a basic Cartesian join on my data set would be  8.0 × 10^12 rows, it makes sense that I needed  to reduce the original data set to something a little more manageable. I created a couple temporary tables to store the exact ranges I'm looking for, which drastically sped up the process.
There are only about 4,700,000 entries to cross reference with the new tables.
The total time is only about 45 - 60 seconds now, including creating the temporary tables.

create temporary table usr select user,addr from user_activity where date='2008-07-16' and user = 'testuser' and addr != '' and addr != '-' group by user,addr;
And one from the firewall log
create temporary table fw select * from firewall_traffic where date='2008-07-16';
select user,dstname,count(*)  from usr join fw on usr.addr = fw.src where group by user,dstname;

Select allOpen in new window