mysql - 在 MySql 中对 Varchar 列设置索引

Question

在 MySql 中，我有一个 UNION 查询，执行时间很长。查询看起来像

(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)

问题是为了获得上述查询的最佳性能，需要对哪些列建立索引以及为什么？

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

Answer 1

我索引了以下内容1. 身份证号码2. 日期3. 标志