mysql primary key returning less results than compound composite index

Question

I have inherited a database schema which has some design issues

Note that there are another 9 keys on the table which I haven't listed below, the keys in question look like

+-------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                   | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY                    |            1 | userid       | A         |         604 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | userid_2                   |            1 | userid       | A         |         604 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | userid_2                   |            2 | age          | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | userid_2                   |            3 | image        | A         |         604 |      255 | NULL   | YES  | BTREE      |         |               |
| users |          1 | userid_2                   |            4 | gender       | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | userid_2                   |            5 | last_login   | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | userid_2                   |            6 | latitude     | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | userid_2                   |            7 | longitude    | A         |         604 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

In a table with the following fields.

+--------------------------------+---------------------+------+-----+-------------------+----------------+
| Field                          | Type                | Null | Key | Default           | Extra          |
+--------------------------------+---------------------+------+-----+-------------------+----------------+
| userid                         | int(11)             | NO   | PRI | NULL              | auto_increment |
| age                            | int(11)             | YES  |     | NULL              |                |
| image                          | varchar(500)        | YES  |     |                   |                |
| gender                         | varchar(10)         | YES  |     | NULL              |                |
| last_login                     | timestamp           | YES  | MUL | NULL              |                |
| latitude                       | varchar(20)         | YES  | MUL | NULL              |                |
| longitude                      | varchar(20)         | YES  |     | NULL              |                |
+--------------------------------+---------------------+------+-----+-------------------+----------------+

Running an explain statement and forcing it to use userid_2, it uses 522 rows

describe SELECT userid, age FROM users USE INDEX(userid_2) WHERE `userid` >=100 and age >27   limit 10 ;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | index | userid_2      | userid_2 | 941     | NULL |  522 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.02 sec)

if I don't force it to use the index it is just using the primary key, which only consists of the userid and only uses 261 rows

mysql> describe SELECT userid, age FROM users  WHERE userid >=100 and age >27   limit 10 ;
+----+-------------+-------+-------+--------------------------------------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys                              | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+--------------------------------------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | PRIMARY,users_user_ids_key,userid,userid_2 | PRIMARY | 4       | NULL |  261 | Using where |
+----+-------------+-------+-------+--------------------------------------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Questions

1. Why is it examining more rows when it uses the compound composite index?
2. Why isn't the query using the userid_2 index if its not specified in the query?

Answer 1

That row count is only an estimate based on indexed value distribution.

You have two options:

1. Execute ANALYZE TABLE mytable to recalculate distributions and then re-try the describe
2. Don't worry about stuff that doesn't matter... rows is just an estimate anyway