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 | |
+------------------+-------------+------+-----+---------+-------+
tableuser_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 theuser_activity table, the queries are awfully slow.
Am I missing some obvious method of making the query faster?
