Hello,

I'm using MariaDB 13.3.14 I have a very simple test table with an email column and I am using |ANALYZE|to see the behavior of the indexes.

If I understand correctly the output of |ANALYZE|, '|rows'|indicates the estimate of records accessed in the table, and |'r_rows'|the actual number of accesses. (Am I correct?)

If yes, the issue is that, for a query as simple as:

|    SELECT*FROMuserWHEREemail ='user@domain.com'| |||    -- user@domain.com exists in email column and it is unique| || If I create a normal index in the |'email'|field, |r_rows=|1. However, if I create a unique index, |r_rows|=|NULL|.

If I understand correctly, a NULL value on 'r_rows' means that no access to data has been performed ( https://mariadb.com/kb/en/library/analyze-statement/ ).

So my question is, if I have a valid result, how can it be that the table is not accessed? How is the data retrieved when a unique index is used?

Here is my output (I remember that the email in the query exists):

MariaDB [test2]> create index email on usuario(email);     Query OK, 95236 rows affected (0.451 sec)     Records: 95236  Duplicates: 0  Warnings: 0         MariaDB [test2]> analyze select * from usuario where email = 'nec.urna.et@volutpatornare.com';     +------+-------------+---------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+     | id   | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                 |     +------+-------------+---------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+     |    1 | SIMPLE      | usuario | ref  | email         | email | 302     | const |    1 |   1.00 |   100.00 |     100.00 | Using index condition |     +------+-------------+---------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+     1 row in set (0.001 sec)         MariaDB [test2]> drop index email on usuario;     Query OK, 95236 rows affected (0.237 sec)     Records: 95236  Duplicates: 0  Warnings: 0         MariaDB [test2]> create unique index email on usuario(email);     Query OK, 95236 rows affected (0.484 sec)     Records: 95236  Duplicates: 0  Warnings: 0         MariaDB [test2]> analyze select * from usuario where email = 'nec.urna.et@volutpatornare.com';     +------+-------------+---------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+     | id   | select_type | table   | type  | possible_keys | key   | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra |     +------+-------------+---------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+     |    1 | SIMPLE      | usuario | const | email         | email | 302     | const |    1 |   NULL |   100.00 |       NULL |       |     +------+-------------+---------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+     1 row in set (0.001 sec)

Regards.

||