mysql没加引号导致全表扫描_mysql-没有全表扫描的优先级/级联日期时间

SELECT IF(priority_date, priority_date, created_at) as created_at

FROM table

WHERE IF(priority_date , priority_date , created_at)

BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59';

从性能角度来看,执行此查询的最佳方法是什么？

我有一个很大的表,有两个日期时间. created_at和priority_date.

priority_date并不总是存在的,但如果存在,则应该是要查询的内容,否则将归还created_at. created_at总是在创建行时生成.上面的查询导致(几乎)全表扫描.

初始查询的解释计划：

+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

| 1 | SIMPLE | table | ALL | NULL | NULL | NULL | NULL | 444877 | Using where |

+------+-------------+-----------------+------+---------------+------+---------+------+--------+-------------+

我还应注意,priority_date或created_at不一定都在单个行中的相关时间范围内.所以做这样的事情：

WHERE priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'

OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'

如果priority_date是2017-10-04 23:10:43并且created_at是2017-10-10 01:23:45可能会给出不好的结果

所述表格的当前行：582739

WHERE priority_date BETWEEN …的计数：3908

在BETWEEN …处创建的计数：3437

示例说明仅在WHERE BETWEEN之间查询的一列：

+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+

| 1 | SIMPLE | table | range | table_created_at_index | table_created_at_index | 5 | NULL | 3436 | Using index condition |

+------+-------------+-----------------+-------+----------------------------------+----------------------------------+---------+------+------+-----------------------+

显然,中频并不是最有效的.列被索引,并且各个行的解释与解释计划中的行计数匹配.如何利用优先级/后备查询而不会造成性能损失？

编辑

我能够弄清楚的最好的东西(但是哇,那是冗长和复制/粘贴的感觉)

SELECT IF(priority_date, priority_date, created_at) as created_at, priority_date

FROM table

WHERE priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'

OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59'

HAVING ((priority_date AND priority_date BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59')

OR created_at BETWEEN '2017-10-10 00:00:00' AND '2017-10-10 23:59:59');

及其说明计划：

+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+

| 1 | SIMPLE | table | index_merge | table_priority_date_index,table_created_at_index | table_priority_date_index,table_created_at_index | 6,5 | NULL | 7343 | Using sort_union(table_priority_date_index,table_created_at_index); Using where |

+------+-------------+-----------------+-------------+-----------------------------------------------------------------------+-----------------------------------------------------------------------+---------+------+------+------------------------------------------------------------------------------------------------------+