[
https://issues.apache.org/jira/browse/AIRFLOW-1495?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16127977#comment-16127977
]
ASF subversion and git services commented on AIRFLOW-1495:
----------------------------------------------------------
Commit 67b47c958903a2297916b44e97adc289d6184b5a in incubator-airflow's branch
refs/heads/master from [~erod]
[ https://git-wip-us.apache.org/repos/asf?p=incubator-airflow.git;h=67b47c9 ]
[AIRFLOW-1495] Fix migration on index on job_id
There was a merge conflict on the migration hash
for down revision
at the time that two commits including migrations
were merged.
This commit restores the chain of revisions for
the migrations,
pointing to the last one. The job_id index
migration was regenerated
from the top migration.
Closes #2524 from edgarRd/erod-ti-jobid-index-fix
> TaskInstanceModelView using unindexed column for order_by on default
> --------------------------------------------------------------------
>
> Key: AIRFLOW-1495
> URL: https://issues.apache.org/jira/browse/AIRFLOW-1495
> Project: Apache Airflow
> Issue Type: Bug
> Components: webserver
> Reporter: Edgar Rodriguez
> Assignee: Edgar Rodriguez
>
> Using unindexed columns for ordering query results triggers sorting in the
> database, which makes for slower queries.
> Solution is to use an indexed column or add an index on the desired columns
> if it doesn't exist, see:
> https://github.com/apache/incubator-airflow/blob/master/airflow/www/views.py#L2489
> When checking for indices on task_instance, none is found for {{job_id}}:
> {code}
> mysql> show index from task_instance;
> +---------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name
> | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
> Index_comment |
> +---------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> | task_instance | 0 | PRIMARY | 1 | task_id
> | A | 71 | NULL | NULL | | BTREE | |
> |
> | task_instance | 0 | PRIMARY | 2 | dag_id
> | A | 80 | NULL | NULL | | BTREE | |
> |
> | task_instance | 0 | PRIMARY | 3 | execution_date
> | A | 30056 | NULL | NULL | | BTREE | |
> |
> | task_instance | 1 | ti_dag_state | 1 | dag_id
> | A | 18 | NULL | NULL | | BTREE | |
> |
> | task_instance | 1 | ti_dag_state | 2 | state
> | A | 28 | NULL | NULL | YES | BTREE | |
> |
> | task_instance | 1 | ti_pool | 1 | pool
> | A | 1 | NULL | NULL | YES | BTREE | |
> |
> | task_instance | 1 | ti_pool | 2 | state
> | A | 3 | NULL | NULL | YES | BTREE | |
> |
> | task_instance | 1 | ti_pool | 3 | priority_weight
> | A | 16 | NULL | NULL | YES | BTREE | |
> |
> | task_instance | 1 | ti_state_lkp | 1 | dag_id
> | A | 18 | NULL | NULL | | BTREE | |
> |
> | task_instance | 1 | ti_state_lkp | 2 | task_id
> | A | 82 | NULL | NULL | | BTREE | |
> |
> | task_instance | 1 | ti_state_lkp | 3 | execution_date
> | A | 28037 | NULL | NULL | | BTREE | |
> |
> | task_instance | 1 | ti_state_lkp | 4 | state
> | A | 29354 | NULL | NULL | YES | BTREE | |
> |
> | task_instance | 1 | ti_state | 1 | state
> | A | 3 | NULL | NULL | YES | BTREE | |
> |
> +---------------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
> {code}
> So, when executing a query similar to the one for the view paging, the query
> explain shows the required filesort:
> {code}
> mysql> explain select * from task_instance order by job_id limit 500 offset
> 500;
> +----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
> | id | select_type | table | partitions | type | possible_keys | key
> | key_len | ref | rows | filtered | Extra |
> +----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
> | 1 | SIMPLE | task_instance | NULL | ALL | NULL | NULL
> | NULL | NULL | 30119 | 100.00 | Using filesort |
> +----+-------------+---------------+------------+------+---------------+------+---------+------+-------+----------+----------------+
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
