Re: How to get acceptable/repeatable performance on huge table

[Aftab Khan]

>We have good peformance with that today but it would become worse with an ordinary Uid index - don't you agree?

No, the new index would be more efficient .

Let's compare EXPLAIN output i.e. after adding secondary index and using original index:

mysql> explain select adId, count(userUId) from UserVisits where adId < 25000 group by adId;
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | UserVisits | index | adId          | adId | 12      | NULL |    4 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(adId) from UserVisits where userUId < 25000 group by userUId;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                     |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | UserVisits | index | NULL          | PRIMARY | 12      | NULL |    4 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec

Edited 1 time(s). Last edit at 08/31/2012 08:16AM by Aftab Khan.