blocks|key|1755601|text|“解释”是什么意思？(运行EXPLAIN+SELECT+*+FROM+transactionlist+date(TranDateTime)+=+'2008-08-17')|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755602|如果由于date()函数而没有使用您的索引，那么范围查询应该运行得很快：|1755603|SELECT+*+FROM+transactionlist+TranDateTime+>=+'2008-08-17‘和TranDateTime+<+'2008-08-18’|1755604|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

What does 'explain' say? (run EXPLAIN SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17')

If it's not using your index because of the date() function, a range query should run fast:

SELECT * FROM transactionlist where TranDateTime >= '2008-08-17' AND TranDateTime &lt; '2008-08-18'

blocks|key|1755632|text|我不知道mySql的细节，但是仅仅对日期字段进行完整的索引有什么坏处呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755633|然后只需搜索：|1755634|+select+*+from+translist+
+++++where+TranDateTime+>+'2008-08-16+23:59:59'
++++++++and+TranDateTime+<+'2008-08-18+00:00:00'|code-block|syntax|javascript|1755635|如果索引是b树或其他合理的索引，应该很快就能找到这些索引。|1755636|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I don't know about the specifics of mySql, but what's the harm in just indexing the date field in its entirety?

Then just search:

<pre><code> select * from translist 
 where TranDateTime &gt; '2008-08-16 23:59:59'
 and TranDateTime &lt; '2008-08-18 00:00:00'
</code></pre>

If the indexes are b-trees or something else that's reasonable, these should get found quickly.

blocks|key|1755723|text|如果我没记错的话，这将运行整个表扫描，因为您正在通过一个函数传递列。MySQL将顺从地为每一列运行函数，绕过索引，因为查询优化器不能真正知道函数的结果。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755724|我会这样做：|1755725|SELECT+*+FROM+transactionlist+
WHERE+TranDateTime+BETWEEN+'2008-08-17'+AND+'2008-08-17+23:59:59.999999';|code-block|syntax|javascript|1755726|这应该会给你在2008-08-17发生的所有事情。|1755727|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If I remember correctly, that will run a whole table scan because you're passing the column through a function. MySQL will obediently run the function for each and every column, bypassing the index since the query optimizer can't really know the results of the function.

What I would do is something like:

<pre><code>SELECT * FROM transactionlist 
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';
</code></pre>

That should give you everything that happened on 2008-08-17.

blocks|key|1755769|text|我不想听起来很可爱，但一种简单的方法是添加一个仅包含日期部分和索引的新列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755770|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I don't mean to sound cute, but a simple way would be to add a new column that only contained the date part and index on that.

blocks|key|1755852|text|Valeriy+Kravchuk在MySQL网站上就这个问题提出了功能请求，说要使用这种方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755853|“同时，您可以使用字符列将DateTime值存储为字符串，只对前N个字符进行索引。在MySQL+5中谨慎使用触发器，您可以基于此思想创建一个相当健壮的解决方案。”|1755854|您可以编写一个非常简单的例程来添加此列，然后使用触发器保持此列的同步。这个字符串列上的索引应该非常快。|1755855|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Valeriy Kravchuk on a feature request for this very issue on the MySQL site said to use this method.

"In the meantime you can use character columns for storing DATETIME values as strings, with only first N characters being indexed. With some careful usage of triggers in MySQL 5 you can create a reasonably robust solution based on this idea."

You could write a routine pretty easy to add this column, and then with triggers keep this column synced up. The index on this string column should be pretty quick.

blocks|key|1755937|text|您不能只在日期部分上创建索引。你有必要这么做吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1755938|即使您可以只在日期部分创建索引，优化器也可能不会将其用于上述查询。|1755939|我想你会发现|1755940|SELECT+*+FROM+transactionlist+WHERE+TranDateTime+BETWEEN+'2008-08-17'+AND+'2008-08-18'|code-block|syntax|javascript|1755941|是高效的，并且可以做你想做的事情。|1755942|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can't create an index on just the date part. Is there a reason you have to?

Even if you could create an index on just the date part, the optimiser would probably still not use it for the above query.

I think you'll find that 

<pre><code>SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'
</code></pre>

Is efficient and does what you want.

blocks|key|1541072|text|我不知道mySQL的细节，但是仅仅对日期字段进行完整的索引有什么坏处呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541073|如果你对*树，散列，..。已不存在，因为要获取值，必须调用该函数。但是，因为您不知道前面的结果，所以必须对表进行全面扫描。|1541074|没有什么要添加的。|1541075|也许你的意思是像计算的(计算的？)索引...但到目前为止，我只在Intersystems+Caché中看到过这一点。我不认为在关系数据库(AFAIK)中存在这种情况。|1541076|在我看来，一个好的解决方案是(更新的clintp示例)：|1541077|SELECT+*+FROM+translist+
WHERE+TranDateTime+>=+'2008-08-17+00:00:00.0000'
++AND+TranDateTime+<+'2008-08-18+00:00:00.0000'|code-block|syntax|javascript|1541078|在我看来，是使用00:00:00.0000还是00:00没有区别(我通常以这种格式使用它)。|offset|length|style|CODE|1541079|entityMap^0|0|0|0|0|0|0|8|D|N|5|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|10|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|12|8|@$Q|13|R|14|S|T]|$Q|15|R|16|S|T]]|9|@]|A|$]]|$1|U|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|V|$]]

I don't know about the specifics of mySQL, but what's the harm in just indexing the date field in its entirety?

If you use functional magic for * trees, hashes, ... is gone, because for obtaining values you must call the function. But, because you do not know the results ahead, you have to do a full scan of the table.

There is nothing to add.

Maybe you mean something like computed (calculated?) indexes... but to date, I have only seen this in Intersystems Caché. I don't think there's a case in relational databases (AFAIK).

A good solution, in my opinion, is the following (updated clintp example):

<pre><code>SELECT * FROM translist 
WHERE TranDateTime &gt;= '2008-08-17 00:00:00.0000'
 AND TranDateTime &lt; '2008-08-18 00:00:00.0000'
</code></pre>

Whether you use <code>00:00:00.0000</code> or <code>00:00</code> in my opinion makes no difference (I've generally used it in this format).

blocks|key|1756053|text|创建一个仅包含日期convert(datetime,+left(date_field,10))的新字段，然后对其进行索引。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1756054|entityMap^0|9|12|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Create a new fields with just the dates <code>convert(datetime, left(date_field,10))</code> and then index that.

blocks|key|1541158|text|一个非常好的解决方案是使用时间戳作为时间，而不是日期时间。它被存储为INT，并且被索引得足够好。就我个人而言，我在transactions表上遇到了这样的问题，大约有几百万条记录，而且速度很慢，最后我指出这是由错误的索引字段(datetime)造成的。现在它运行得非常快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541159|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

The one and good solution that is pretty good working is to use timestamp as time, rather than datetime.
It is stored as INT and being indexed good enough.
Personally i encountered such problem on transactions table, that has about million records and slowed down hard, finally i pointed out that this caused by bad indexed field (datetime).
Now it runs very quick.

blocks|key|1541242|text|如果可以修改该表，或者您正在编写新的表，请考虑将日期和时间存储在具有各自类型的单独列中。您可以通过使用更小的键空间和更少的存储来获得性能(与从datetime派生的仅日期列相比)。这也使得它可以在复合键中使用，甚至可以在其他列之前使用。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1541243|在OP的例子中：|1541244|%2B-------------------%2B------------------%2B------%2B-----%2B---------%2B----------------%2B
%7C+Field+++++++++++++%7C+Type+++++++++++++%7C+Null+%7C+Key+%7C+Default+%7C+Extra++++++++++%7C
%2B-------------------%2B------------------%2B------%2B-----%2B---------%2B----------------%2B
%7C+TransactionNumber+%7C+int(10)+unsigned+%7C+NO+++%7C+PRI+%7C+NULL++++%7C+auto_increment+%7C
%7C+WagerId+++++++++++%7C+int(11)++++++++++%7C+YES++%7C+MUL+%7C+0+++++++%7C++++++++++++++++%7C
%7C+TranNum+++++++++++%7C+int(11)++++++++++%7C+YES++%7C+MUL+%7C+0+++++++%7C++++++++++++++++%7C
%7C+TranDate++++++++++%7C+date+++++++++++++%7C+NO+++%7C+++++%7C+NULL++++%7C++++++++++++++++%7C
%7C+TranTime++++++++++%7C+time+++++++++++++%7C+NO+++%7C+++++%7C+NULL++++%7C++++++++++++++++%7C
%7C+Amount++++++++++++%7C+double+++++++++++%7C+YES++%7C+++++%7C+0+++++++%7C++++++++++++++++%7C
%7C+Action++++++++++++%7C+smallint(6)++++++%7C+YES++%7C+++++%7C+0+++++++%7C++++++++++++++++%7C
%7C+Uid+++++++++++++++%7C+int(11)++++++++++%7C+YES++%7C+++++%7C+1+++++++%7C++++++++++++++++%7C
%7C+AuthId++++++++++++%7C+int(11)++++++++++%7C+YES++%7C+++++%7C+1+++++++%7C++++++++++++++++%7C
%2B-------------------%2B------------------%2B------%2B-----%2B---------%2B----------------%2B|code-block|syntax|javascript|1541245|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

If modifying the table is an option, or you're writing a new one, consider storing date and time in separate columns with respective types. You get performance by having a much smaller key space, and reduced storage (compared to a date-only column derived from a datetime). This also makes it feasible to use in compound keys, even before other columns.

In OP's case:

<pre><code>+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO | PRI | NULL | auto_increment |
| WagerId | int(11) | YES | MUL | 0 | |
| TranNum | int(11) | YES | MUL | 0 | |
| TranDate | date | NO | | NULL | |
| TranTime | time | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+
</code></pre>

How do I create an index on the date part of DATETIME field?

<pre><code>mysql&gt; SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO | PRI | NULL | auto_increment |
| WagerId | int(11) | YES | MUL | 0 | |
| TranNum | int(11) | YES | MUL | 0 | |
| TranDateTime | datetime | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
</code></pre>

TranDateTime is used to save the date and time of a transaction as it happens

My Table has over 1,000,000 records in it and the statement 

<pre><code>SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17' 
</code></pre>

takes a long time.

EDIT: 

Have a look at this blog post on "<a href="http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/" rel="noreferrer">Why MySQL’s DATETIME can and should be avoided</a>"

How does one create an index on the date part of DATETIME field in MySql

数据库

MySQL

如何在日期时间字段的日期部分创建索引？mysql> SHOW COLUMNS FROM transactionlist;+-------------------+------------------+------+-----+---------+----------------+| Field             ...

问如何在MySql中的DATETIME字段的日期部分创建索引
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySql中的DATETIME字段的日期部分创建索引EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySql中的DATETIME字段的日期部分创建索引
EN