optimization - Mysql best index(es) for an agenda table

Question

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 ;

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) a list of all appointments of an user.

Example:

SELECT * from agenda where id_user = '1';

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

Example:

SELECT * from agenda where start_day = CURDATE();

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

Example:

SELECT * from agenda where id_customer = 'X';

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

Example:

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

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

Example:

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

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?

Thanks to all.

Answer 1

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)

This one

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;

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 %';

However FULLTEXT(title) index together with MATCH(title) AGAINST('+closing' IN BOOLEAN MODE) would be fast.

More on building indexes.