Question 21392 in Mysql Question posted in
The official MySQL documentation can be found here.

Create MySQL index to birthdays

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.

Tags:

2

Answers


  1. 0

    Use functional index.

    KEY `idx_datanasc` (`datanasc`),
KEY `idx_day` ((day(`datanasc`))),
KEY `idx_month` ((month(`datanasc`))),
    Login or Signup to reply.
  2. 0

    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 |
+----+-------------+-------+------------+-------+------------------+------------------+---------+------+------+----------+-------------+

    An index cannot optimize both the condition on email and the condition on the year expression, 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search