I have a database with table that contains tasks. Tasks have a lifecycle. The status of the task's lifecycle can change. These state transitions are stored in a separate table tasktransitions. Now I wrote a query to find all open/reopened tasks and recently changed tasks but I already see with a rather small number of tasks (<1000) that execution time has becoming very long (>0.5s).

Tasks

+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| taskid      | int(11) | NO   | PRI | NULL    | auto_increment |
| description | text    | NO   |     | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Tasktransitions

+------------------+-----------+------+-----+-------------------+----------------+
| Field            | Type      | Null | Key | Default           | Extra          |
+------------------+-----------+------+-----+-------------------+----------------+
| tasktransitionid | int(11)   | NO   | PRI | NULL              | auto_increment |
| taskid           | int(11)   | NO   | MUL | NULL              |                |
| status           | int(11)   | NO   | MUL | NULL              |                |
| description      | text      | NO   |     | NULL              |                |
| userid           | int(11)   | NO   |     | NULL              |                |
| transitiondate   | timestamp | NO   |     | CURRENT_TIMESTAMP |                |
+------------------+-----------+------+-----+-------------------+----------------+

Query

SELECT tasks.taskid,tasks.description,tasklaststatus.status
FROM tasks
LEFT OUTER JOIN
(
    SELECT tasktransitions.taskid,tasktransitions.transitiondate,tasktransitions.status
    FROM tasktransitions
    INNER JOIN 
    (
        SELECT taskid,MAX(transitiondate) AS lasttransitiondate
        FROM tasktransitions
        GROUP BY taskid
    ) AS tasklasttransition ON tasklasttransition.lasttransitiondate=tasktransitions.transitiondate AND tasklasttransition.taskid=tasktransitions.taskid
) AS tasklaststatus ON tasklaststatus.taskid=tasks.taskid
WHERE tasklaststatus.status IS NULL OR tasklaststatus.status=0 or tasklaststatus.transitiondate>'2013-09-01';

I'm wondering if the database structure is best choice performance wise. Could adding indexes help? I already tried to add some but I don't see great improvements.

+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table           | Non_unique | Key_name       | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tasktransitions |          0 | PRIMARY        |            1 | tasktransitionid | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | taskid_date_ix |            1 | taskid           | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | taskid_date_ix |            2 | transitiondate   | A         |         896 |     NULL | NULL   |      | BTREE      |         |               |
| tasktransitions |          1 | status_ix      |            1 | status           | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------+------------+----------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Any other suggestions?