Sql performance

[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!

[felipe1]

You should try posting that question on a SQL forum, or maybe on Stackoverflow.com

[trademak]

Which database are you using? Make sure you got the right indexes for this query to start with.

Best regards,

[yahekia]

We are using mysql, and the indexes we have are from the create script and one custom index to improve another queries, but with this one no indexes helps us to improve the performance.

So our solution for now is transform this variables into jpa entitiy instead of variables, because we were using the variables to extend the task entity. And in the future, we will add an external index like elasticsearch to avoid this kind of problems with orders.

Do you think is a good approach?? We plan to have more than one million of task….

[trademak]

Yes that's a perfectly fine approach.

Best regards,