Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5.31
-
None
-
None
Description
explain select SQL_NO_CACHE lots.productId, MAX(lots.tsExpires) FROM lots WHERE productID in(60195,60199) GROUP BY productId;
|
|
|
+------+-------------+-------+-------+---------------------------+----------+---------+------+------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------------------+----------+---------+------+------+---------------------------------------+
|
| 1 | SIMPLE | lots | range | productId,prodId,idx_sky1 | idx_sky1 | 4 | NULL | 1 | Using where; Using index for group-by |
|
+------+-------------+-------+-------+---------------------------+----------+---------+------+------+---------------------------------------+
|
1 row in set (6,91 sec)
|
|
|
mysql> explain select SQL_NO_CACHE DISTINCT lots.productId, MAX(lots.tsExpires) FROM lots WHERE productID in(60195,60199) GROUP BY productId;
|
+------+-------------+-------+-------+---------------------------+-----------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------------------+-----------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | lots | range | productId,prodId,idx_sky1 | productId | 4 | NULL | 2 | Using where; Using index |
|
+------+-------------+-------+-------+---------------------------+-----------+---------+------+------+--------------------------+
|
1 row in set (0,04 sec)
|
Index on productId,tsExpires
| productId | int(11) unsigned | NO | MUL | NULL
|
| tsExpires | timestamp | NO | MUL | 0000-00-00 00:00:00 |
|