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?

Do you have index on username and date columns?

No. Would that help significantly?

I am not sure about this. You have to test it yourself by implementing both approaches.

Don't forget to index email and username in tables before uisng JOIN.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.