CODEWITHSUNDEEP
mysqlsql
badCoder
badCoder

Reputation: 770

Is it possible to improve the performance of query with distinct?

There is following query:

        "SELECT DISTINCT LEVEL, ID, R1, R2, R3, R4, R5" +
        " FROM custom_table +
        " WHERE fromDate=:startDate 
        " AND toDate=:endDate, 
        " AND AccountIds=[ :accountIds ]
        " AND LEVEL IN (:Levels)"

Some columns of the table (exclude dates and other non-important columns for us):

ID INTEGER NOT NULL,
LEVEL INTEGER,
R1 VARCHAR(50),
R2 VARCHAR(50),
R3 VARCHAR(50),
R4 VARCHAR(50),
R5 VARCHAR(50)

Simple data:

ID,LEVEL,R1,R2,R3,R4,R5
id1,1,TOTAL,null,null,null,null
id1,2,TOTAL,A,null,null,null
id1,2,TOTAL,B,null,null,null
id2,1,TOTAL,null,null,null,null
id2,2,TOTAL,A,null,null,null
id2,3,TOTAL,B,C,null,null

Current query is running more than 1s, usually it returns between 100 and 1000 records, I want to improve the performance of this query. I have tried to rewrite it with GROUP BY clause but it is overkill because there are no aggregation and it is redundant, I think.

Maybe there are ways to improve this query to fetch data a bit faster? I hope I've provided enough information here. Database is custom, NO_SQL giant under the hood but syntax of our database bridge is very similar to MySQL.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You would seem to want an index on: (fromDate, toDate, accountId, levels).

The first three columns are all used for = comparisons, so they can be in any order.

Upvotes: 1

Related Questions