How to force index elegantly in ebean?

[magicliang]

I have a method like this:

        final ExpressionList<LeadsActionInfo> expressionList = getServer()
                .find(ActionInfo.class)
                .where()
                .in("accountId", accountIdList)
                .eq("clickId", clickId)
                .gt("actionTime", beginDate);
        if (CollectionUtils.isNotEmpty(actionSubTypes)) {
            expressionList.in("actionSubType", actionSubTypes);
        }

        final Query<LeadsActionInfo> query = expressionList.order("actionTime DESC");
        return query.findList();

I want to add hint to help mysql to force using some index, how can I achieve this goal? I know native query could help but I would prefer fluent api instead.

Any suggestion would be helpful.

[jnehlmeier]

Did you mean how to tell ebean to generate a query that looks like select * from ... use index(idx1, idx2) where ... which forces mysql to only use the specified indexes? Or do you have a general issue that mysql does not use an index which you think it should use?

[magicliang]

let me elaborate this:

the original codes are like:

        final ExpressionList<LeadsActionInfo> expressionList = getServer()
                .find(ActionInfo.class)
                .where()
                .in("accountId", accountIdList)
                .eq("clickId", clickId)
                .gt("actionTime", beginDate);
        if (CollectionUtils.isNotEmpty(actionSubTypes)) {
            expressionList.in("actionSubType", actionSubTypes);
        }

        final Query<LeadsActionInfo> query = expressionList.order("actionTime DESC");
        return query.findList();

which can be translate in another format using raw sql like:

String sql = "SELECT * FROM LeadsActionInfo_ FORCE INDEX (idx_clickId) WHERE accountId in "
                + "(:accountIdList) AND clickId = :clickId AND actionTime > :beginDate ";
        sql += "ORDER BY actionTime DESC";
        final DtoQuery<LeadsActionInfo> leadsActionInfoDtoQuery = EBeanEnvUtil.getCustomerServer()
                .findDto(LeadsActionInfo.class, sql)
                .setParameter("accountIdList", accountIdList)
                .setParameter("clickId", clickId)
                .setParameter("beginDate", beginDate);

        return leadsActionInfoDtoQuery.findList();

I know this would work, but what I prefer is:

final ExpressionList<LeadsActionInfo> expressionList = getServer()
                .find(ActionInfo.class)
                .where()
                .in("accountId", accountIdList)
                .eq("clickId", clickId)
                .gt("actionTime", beginDate);
        if (CollectionUtils.isNotEmpty(actionSubTypes)) {
            expressionList.in("actionSubType", actionSubTypes);
        }

        final Query<LeadsActionInfo> query = expressionList.order("actionTime DESC");
        return query
          .addHint("FORCE INDEX (idx_clickId)") // I know there is no this api yet.
          .findList();

How can I get something like addHint api?

[skatsumata]

Ah, as I thought, the current version of Ebean does not allow the specification of FORCE INDEX except through raw SQL, does it?
I would really appreciate it if you could implement this feature (addHint).