Slow query. Wrong database structure?
- by Tin
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?
