Hi all.
I am writing a utility to manage virtual users on a mailserver w/ mysql backed. Unfortunately I am having performance issues when querying for the user's last access date/time.
DB has this structure
table
users
+------------------+-------------+------+-----+---------+-------+
| FIELD | Type | NULL | Key | DEFAULT | Extra |
+------------------+-------------+------+-----+---------+-------+
| email | VARCHAR(80) | NO | PRI | NULL | |
| force_change_pwd | INT(1) | NO | | 0 | |
| password | MD5(20) | NO | | NULL | |
| creation_date | DATETIME | YES | | NULL | |
+------------------+-------------+------+-----+---------+-------+
table
user_activity
+----------------+--------------+------+-----+---------+----------------+
| FIELD | Type | NULL | Key | DEFAULT | Extra |
+----------------+--------------+------+-----+---------+----------------+
| id | INT(11) | NO | PRI | NULL | AUTO_INCREMENT |
| event | VARCHAR(30) | NO | MUL | NULL | |
| username | VARCHAR(128) | NO | | NULL | |
| domain | VARCHAR(128) | NO | MUL | NULL | |
| remote_address | VARCHAR(255) | NO | | NULL | |
| DATE | DATETIME | NO | MUL | NULL | |
| comments | VARCHAR(255) | YES | | | |
+----------------+--------------+------+-----+---------+----------------+
In my PHP page, I have the following code
$mbox_users = 'SELECT email, creation_date FROM users ORDER BY email ASC';
$result = mysql_query($mbox_users) OR die ("There was a problem with the SQL query: " . mysql_error());
while($row = mysql_fetch_assoc($result))
{
$email=$row['email'];
$date=$row['creation_date'];
if (is_null($date)) {
$date = "N/A";
}
$mbox_access="SELECT date FROM user_activity WHERE username = '".$row['email']."' ORDER BY date DESC LIMIT 1";
$result_access = mysql_query($mbox_access) OR die ("There was a problem with the SQL query: " . mysql_error());
$last = mysql_fetch_assoc($result_access);
echo '<li>'.$email.' (<b>Created: </b>'.$date.' - <b>Last Access: </b>'.$last['date'].')</li>';
Unfortunately with +20k users and over 100k records in the
user_activity table, the queries are awfully slow.
Am I missing some obvious method of making the query faster?
Unsolved 
