An MySQL query to get latest record for multiple distinct items in a table

Question

I'm having trouble pulling the data out of a table as it is taking too long and slowing down the system.

Here is the reference table:

CREATE TABLE IF NOT EXISTS `User_Table` (
    `user_table_id`  int unsigned        not null auto_increment,
    `user_id`        int unsigned        not null,
    `unixtimes`      int unsigned        not null,
    `status`         char(1)             not null default '',
    `text1`          text                not null,
    `text2`          text                not null,
PRIMARY KEY (`user_table_id`),
UNIQUE INDEX user_table_1 (`user_id`, `status`, `unixtimes`),
UNIQUE INDEX User_Table_2 (user_id, unixtimes, status)
);

status can have one of four values;

• 'a' = approved
• 'd' = denied
• 'p' = pending
• 's' = skipped

I am trying to join the same table multiple times. The end goal is single record with the user_id (so it can be joined to the user table among other things), the most recent pending text, and the most recent approved text if it exists so that they can be compared.

| user_id | login | pending_text1 | pending_text2 | current_text1 | current_text1 |
|---------|-------|---------------|---------------|---------------|---------------|
| 8675309 | Bob   | Second entry  | Second other  | First entry   | First other   |

If it makes a difference, there should only ever be one record marked as pending for any given user_id. The pending records are reviewed and either updated to be approved or denied. If a new pending record comes in before the existing pending record has been reviewed, the old record is updated to skipped leaving just the one pending.

It should also be noted that I'm mainly concerned with looking at the latest pending records, and there might not be any approved record. For instance, during the first review there can only be a pending. That's why I've be using the LEFT JOIN.

Here is a stripped down version of the query I've been using but it's taking at minimum 4-5 seconds each time, but with less than 100k records. Since the volume of records can only go up, I'm hoping for a better return on the query.

SELECT
    `up`.`user_id` AS 'user_id',
    `u`.`login` AS 'login',
    `up`.`text1` AS 'pending_text1',
    `up`.`text2` AS 'pending_text2',
    `record_current`.`text1` AS 'current_text1',
    `record_current`.`text2` AS 'current_text2'

FROM
    `user_table` up
JOIN
    `user` u
ON    `up`.`user_id` = `u`.`user_id`

LEFT JOIN (
    SELECT
        `up`.*
    FROM
        `user_table` up
    JOIN (
        SELECT
            `user_id`, MAX(`unixtimes`) unixtimes 
        FROM 
            `user_table` 
        WHERE
            `status` = 'a'
        GROUP BY
            `user_id`) all_approved
    ON 
        `up`.`user_id` = `all_approved`.`user_id` AND `up`.`unixtimes` = `all_approved`.`unixtimes`) record_current
ON 
    `up`.`user_id` = `record_current`.`user_id`

WHERE 
    `up`.`status` = 'p'
ORDER BY 
    `up`.`unixtimes`;

Any ideas?

Update: Added a second index : UNIQUE INDEX User_Table_2 (user_id, unixtimes, status) Adding EXPLAIN to question:

| id  | select_type | table        | type   | possible_keys                 | key          | key_len | ref                                         | rows  | filtered | Extra                                        |
|-----|-------------|--------------|--------|-------------------------------|--------------|---------|---------------------------------------------|-------|----------|----------------------------------------------|
| 1   | PRIMARY     | up           | ALL    | User_Table_1,User_Table_2     | null         | null    | null                                        | 93858 | 75.0     | Using where; Using temporary; Using filesort |
| 1   | PRIMARY     | u            | eq_ref | PRIMARY                       | PRIMARY      | 4       | dbase.up.user_id                            | 1     | 100.0    |                                              |
| 1   | PRIMARY     | <derived2>   | ALL    | null                          | null         | null    | null                                        | 82793 | 100.0    |                                              |
| 2   | DERIVED     | <derived3>   | ALL    | null                          | null         | null    | null                                        | 82793 | 100.0    |                                              |
| 2   | DERIVED     | up           | ref    | User_Table_1,User_Table_2     | User_Table_2 | 8       | all_approved.user_id,all_approved.unixtimes | 469   | 100.0    |                                              |
| 3   | DERIVED     | User_Table   | range  | null                          | User_Table_1 | 5       | null                                        | 10    | 100.0    | Using where; Using index for group-by        |