maria-discuss team mailing list archive

[Barrendeitor <barrendeitor@xxxxxxxxx>]

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@xxxxxxxxxx'|
|||    -- user@xxxxxxxxxx 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@xxxxxxxxxxxxxxxxxx';
   
+------+-------------+---------+------+---------------+-------+---------+-------+------+--------+----------+------------+-----------------------+
    | 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@xxxxxxxxxxxxxxxxxx';
   
+------+-------------+---------+-------+---------------+-------+---------+-------+------+--------+----------+------------+-------+
    | 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.





||