在mysql中索引datetime字段是个好主意吗？ [英] Is it a good idea to index datetime field in mysql?

问题描述

我正在设计一个大型数据库。在我的应用程序中，我将有很多行，例如我目前有一个包含400万条记录的表。我的大多数查询都使用datetime子句来选择数据。在mysql数据库中索引datetime字段是一个好主意吗？

I am working on designing a large database. In my application I will have many rows for example I currently have one table with 4 million records. Most of my queries use datetime clause to select data. Is it a good idea to index datetime fields in mysql database?

Select field1, field2,.....,field15
from table where field 20 between now() and now + 30 days 

我正在尝试保持我的数据库运行良好，查询运行顺畅

I am trying to keep my database working good and queries being run smoothly

更多，您认为我应该创建一个高效数据库的想法是什么？

More, what idea do you think I should have to create a high efficiency database?

推荐答案

MySQL建议使用索引有多种原因，包括消除条件之间的行： http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

如果您要在查询中经常使用条件，那么这使得您的datetime列成为索引的绝佳候选者。如果您的唯一条件是 BETWEEN NOW（）和DATE_ADD（NOW（），INTERVAL 30 DAY）并且您没有其他索引，MySQL将不得不做<每个查询都有em>全表扫描。我不确定在30天内生成了多少行，但只要它少于总行数的1/3，就可以更有效地在列上使用索引。

This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) and you have no other index in the condition, MySQL will have to do a full table scan on every query. I'm not sure how many rows are generated in 30 days, but as long as it's less than about 1/3 of the total rows it will be more efficient to use an index on the column.

关于创建高效数据库的问题非常广泛。我要说的是确保它已经标准化并且所有适当的列都被索引（即在连接和where子句中使用的列）。

Your question about creating an efficient database is very broad. I'd say to just make sure that it's normalized and all appropriate columns are indexed (i.e. ones used in joins and where clauses).

这篇关于在mysql中索引datetime字段是个好主意吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！