Setting index in MySql on a Varchar Column

Question

In MySql, I have a UNION query which takes long for execution. The query looks like

(SELECT id, X, Y , date FROM TABLE WHERE date in (SELECT MIN(date) FROM TABLE WHERE flag = 0 AND id = ? AND date BETWEEN ? AND ?) and id = ? and flag=0 ORDER BY id DESC LIMIT 1)

UNION 

(SELECT id, X, Y , date FROM TABLE WHERE date in (SELECT MAX(date) FROM TABLE WHERE flag = 0 AND id = ? AND date BETWEEN ? AND ?) and id = ? and flag = 0 ORDER BY id DESC LIMIT 1)

The question is for best performance of above query, what columns need to be indexed and why ?

id - type int
date - type datetime
flag - type tinyint(4)

score 0 · Accepted Answer · answered Sep 24 '14 at 20:30

0

I indexed following 1. id 2. date 3. flag

answered Sep 24 '14 at 20:30

AdityaKapreShrewsburyBoston

1,143
2
16
37

Setting index in MySql on a Varchar Column

1 Answers1