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 * FROM user WHERE email = '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.