Create MySQL index to birthdays-CodePudding

I have a MySQL table as:

Table cl_clientes:

id int AI PK,
nome varchar(100)  --> name
email varchar(200)
datanasc datetime  ---> here is the birthdate
data_envio_email datetime  --> sent email date

To find the list of persons with birthdates at the current date, I am doing the next:

select
      A.id,
      A.nome,
      A.email,
      A.datanasc,
      A.data_envio_email
      from cl_clientes A
      where
      (A.data_envio_email is null or year(A.data_envio_email)<=year(curdate()))
      and A.email is not null and A.email<>'' and
        (month(A.datanasc)=month(curdate())
        and day(A.datanasc)=day(curdate()))

What column fields should I use to create an index in order to accelerate the results? I thought to create an index in "datanasc", but It doesn´t solve the problem.

CodePudding user response：

Use functional index.

KEY `idx_datanasc` (`datanasc`),
KEY `idx_day` ((day(`datanasc`))),
KEY `idx_month` ((month(`datanasc`))),

CodePudding user response：

I was able to create a test case with a compound functional index on MySQL 8.0:

alter table cl_clientes
 add key ((date_format(datanasc, '%m%d')), (year(coalesce(data_envio_email, '1900-01-01'))));

If you use the same expressions in your WHERE clause as the expressions used in the functional index, then the optimizer can use the index. The EXPLAIN report confirms it is using the index:

explain select *
from cl_clientes A
where
 date_format(datanasc, '%m%d') = date_format(curdate(), '%m%d')
 and year(coalesce(data_envio_email, '1900-01-01')) <= year(curdate())
 and nullif(email, '') is not null;

 ---- ------------- ------- ------------ ------- ------------------ ------------------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type  | possible_keys    | key              | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------- ------------------ ------------------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | A     | NULL       | range | functional_index | functional_index | 24      | NULL |    1 |   100.00 | Using where |
 ---- ------------- ------- ------------ ------- ------------------ ------------------ --------- ------ ------ ---------- -------------

The condition on email cannot be optimized with an index if the condition on the year is indexed, because both are inequality conditions.

You asked in a comment if adding an index has a performance impact on INSERT/UPDATE. Yes, it does. All indexes add some overhead to writes. You will have to make a judgement about whether the performance improvement of SELECT queries is important to you, enough to justify the cost of maintaining the index.