议程表的Mysql最佳索引 [英] Mysql best index(es) for an agenda table

问题描述

CREATE TABLE IF NOT EXISTS `agenda` (
  `id_agenda` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL DEFAULT '0',
  `id_customer` int(11) DEFAULT NULL,
  `type` int(11) NOT NULL DEFAULT '8',
  `title` varchar(255) NOT NULL DEFAULT '',
  `text` text NOT NULL,
  `start_day` date NOT NULL DEFAULT '0000-00-00',
  `end_day` date NOT NULL DEFAULT '0000-00-00',
  `start_hour` time NOT NULL DEFAULT '00:00:00',
  `end_hour` time NOT NULL DEFAULT '00:00:00'
  PRIMARY KEY (`id_agenda`),
  KEY `start_day` (`start_day`),
  KEY `id_customer` (`id_customer`),
  KEY `id_user` (`id_user`),
  KEY `type` (`type`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

让我们遇到这种情况：一个带有200万条记录的议程表的软件，10个活跃的用户。

Let's have this kind of situation: a software with an agenda table with 2 million of records, 10 active users.

支持此类需求的最佳索引配置是什么：

What is the best index configuration for supporting this kind of needs:

1）列表所有用户约会。

1) a list of all appointments of an user.

示例：

SELECT * from agenda where id_user = '1';

2）当天，周或月的所有约会清单。

2) a list of all appointments of the day, or the week, or the month.

示例：

SELECT * from agenda where start_day = CURDATE();

3）链接到客户X的所有约会的列表。

3) a list of all appointments linked to the customer X.

示例：

SELECT * from agenda where id_customer = 'X';

4）例如，一个月内所有类型Y约会的列表。

4) a list of all appointments of type Y in a month for example.

示例：

SELECT * from agenda where type='2' AND MONTH(start_day) = MONTH(CURDATE());

5）标题中包含一些字符串模式的所有约会列表。

5) a list of all appointments having some string pattern inside the title.

示例：

SELECT * from agenda where title LIKE '% closing %';

我问这个因为我读了很多文件，这是一个不好的选择为WHERE子句，ORDER BY，GROUP BY中使用的每个字段都有一个索引......但是对于这种需求，每个字段的索引如何可以避免？
使用复合索引，如果我做对了，我可以使用第二个字段，就好像我正在使用索引的第一个字段，这是正确的吗？

I'm asking that cause i've read in a lot of documents that is a bad choice to have an index for each fields used in WHERE clause, ORDER BY, GROUP BY... but for this kind of needs how is avoidable an index for each field? With a composite index, if i got it right, i can use the second field just if i'm using the first field of the index, is that correct?

感谢所有。

推荐答案

SELECT * from agenda where id_user = '1';
INDEX(id_user)

SELECT * from agenda where start_day = CURDATE();
INDEX(start_day)

SELECT * from agenda where id_customer = 'X';
INDEX(id_customer)

这一个

SELECT  *
    from  agenda
    where  type='2'
      AND  MONTH(start_day) = MONTH(CURDATE());

不是一个好的表述。更改为

is not a good formulation. Change to

SELECT  *
    from  agenda
    where  type='2'
      AND  start_day >= CONCAT(LEFT(CURDATE(), 7), '-01')
      AND  start_day  < CONCAT(LEFT(CURDATE(), 7), '-01') + INTERVAL 1 MONTH;

并添加此综合指数： INDEX（type，start_day）。也可以在类型上删除索引;这将是没用的。

and add this composite index: INDEX(type, start_day). Also get rid of the index on just type; it will be useless.

这个不能按原样优化（因为领先的通配符）：

This one cannot be optimized as is (because of the leading wildcard):

SELECT  *
    from  agenda
    where  title LIKE '% closing %';

然而 FULLTEXT（标题）索引与 MATCH（title）AGAINST（'+关闭'在BOOLEAN模式中）会很快。

更多 。

这篇关于议程表的Mysql最佳索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！