Query optimization

trashed — Sat, 24 Oct 2009 10:04:45 GMT

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 ''.$email.' (Created: '.$date.' - Last Access: '.$last['date'].')
';

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?

DaniWeb IT Discussion Community

Query optimization