AnsweredAssumed Answered

Sql performance

Question asked by yahekia on Oct 29, 2013
Latest reply on Nov 4, 2013 by yahekia
Hello!! I have a problem with an sql, it takes to long to return the data. Maybe this is not the place ask it, but I do not know how to improve it, so any suggestion are wellcome. This is the sql:

SELECT DISTINCT
    TASK.*
FROM
    act_hi_taskinst TASK
INNER JOIN act_hi_varinst VARFLAG
ON
    (
        TASK.ID_ = VARFLAG.TASK_ID_
    AND VARFLAG.NAME_ = 'flag'
    )
ORDER BY VARFLAG.TEXT_ LIMIT 15,15;

  explain:

id            select_type        table      type       possible_keys   key         key_len                ref          rows      Extra
1              "SIMPLE"             "VARFLAG"         "ref"      "ACT_IDX_HI_PROCVAR_NAME_TYPE"                "ACT_IDX_HI_PROCVAR_NAME_TYPE" "767"     "const" 106502  "Using where; Using temporary; Using filesort"
1              "SIMPLE"             "TASK"  "eq_ref"              "PRIMARY"         "PRIMARY"         "194"     "activity_perf.VARFLAG.TASK_ID_"                1              ""
Time : 30~40 segundos.
Count(*) act_hi_taskinst = 50.000 rows
Count(*) act_hi_varinst = 200.000 rows

Thanks!

Outcomes