我正在学习mysql,我有2个表,我必须比较table1主键与table2主键,在成功匹配上,我需要从table2中获得日期最小和最大为此我已经尝试过这些命令,虽然我得到了结果,但需要很长时间请让我知道是否有任何好的方法来处理这种情况。
日期格式如下
mysql> select sdate from table2
| 27-Apr-2000 11:50:00 AM |
| 27-Apr-2000 10:20:00 AM |
| 27-Apr-2000 08:30:00 AM |
| 20-Jan-1999 12:00:00 PM |
command I tried
mysql> select min(str_to_date(m.sdate,'%d-%M-%Y')) as date_min, max(str_to_date(m.sdate,'%d-%M-%Y')) as date_max from ( select distinct p.key1 as key1, p.key2 as key2 from table1 as p ) as T inner join table2 as m on T.key1 = m.key1 and T.key2 = m.key2 where m.sdate !='';
+------------+------------+
| date_min | date_max |
+------------+------------+
| 1989-02-24 | 2011-12-30 |
+------------+------------+
1 row in set, 11396 warnings (18.95 sec)
mysql> select min(str_to_date(m.sdate,'%d-%M-%Y')) as date_min, max(str_to_date(m.sdate,'%d-%M-%Y')) as date_max from ( select p.key1 as key1, p.key2 as key2 from table1 as p ) as T inner join table2 as m on T.key1 = m.key1 and T.key2 = m.key2 where m.sdate !='';
+------------+------------+
| date_min | date_max |
+------------+------------+
| 1989-02-24 | 2011-12-30 |
+------------+------------+
1 row in set, 11442 warnings (18.78 sec)
mysql> select min(str_to_date(m.sdate,'%d-%M-%Y')) as date_min, max(str_to_date(m.sdate,'%d-%M-%Y')) as date_max from table2 as m, table1 as p where p.key1 = m.key1 and p.key2 = m.key2 and m.sdate !='';
+------------+------------+
| date_min | date_max |
+------------+------------+
| 1989-02-24 | 2011-12-30 |
+------------+------------+
1 row in set, 11442 warnings (18.86 sec)
mysql>
没有一个查询能够有效地利用索引(即。范围扫描操作)在VARCHAR
sdate
列上,因为该列被"包装"在查询中的函数中。为了获得这种形式查询的最佳性能,理想情况下,sdate
应该是实际的MySQL DATETIME
或TIMESTAMP
数据类型,甚至是规范格式的VARCHAR
。如果是这种情况,优化器将能够有效地利用索引来快速定位"最小"answers"最大"日期值,而不需要为表中的每一个翻转行计算STR_TO_DATE
函数,并且避免需要排序操作来定位从函数返回的"最小"answers"最大"值。
把那(半咆哮)放在一边…
在一般情况下,要获得与问题中的前两个查询相同的结果,Gordon Linoff回答中建议的形式的查询可能是您的最佳选择。
(我们注意到前两个查询包含一个key2=key1
谓词,第三个查询有一个key2=key2
谓词)
如果table2
中有大量的行,并且这些行中的绝大多数将"匹配"table1
中的一行,并且table2
中有相对较少的不同的(key1,key2)
值,并且(key1,key2)
元组在table1
中是唯一的或几乎唯一的,
有一个外部的机会,这种形式的查询可能会执行得更好:
SELECT MIN(q.sdate_min) AS date_min
, MAX(q.sdate_max) AS date_max
FROM ( SELECT m.key1
, m.key2
, MIN(STR_TO_DATE(m.sdate,'%d-%M-%Y')) AS sdate_min
, MAX(STR_TO_DATE(m.sdate,'%d-%M-%Y')) AS sdate_max
FROM table2 m
GROUP
BY m.key1
, m.key2
) q
JOIN table1 t
ON t.key1 = q.key1
AND t.key2 = q.key2
为了提高内联视图查询的性能,您将需要在table2
上建立一个索引,其前导列为key1
和key2
(按任意顺序),并且还包括sdate
列。例如:
... ON table2 (key1, key2, sdate)
为了提高JOIN
操作的性能,您需要在table1
上创建一个索引,其中key1
和key2
作为索引的前导列。例如:
... ON table1 (key1,key2)
或
... ON table1 (key2,key1)
(这假设您将在第三个查询中使用谓词的形式,即key1=key1和key2=key**2**
如果您将使用key1=key1和key2=key**1**的形式的谓词,那么我们将相应地调整查询和索引
您可以尝试以下方法:
select min(str_to_date(m.sdate,'%d-%M-%Y')) as date_min,
max(str_to_date(m.sdate,'%d-%M-%Y')) as date_max
from table2 m
where exists (select 1
from table1 t
where t.key1 = m.key1 and t.key1 = m.key2
);
table1(key1, key2)
上创建一个索引。