Details
-
Type:
Bug
-
Status: Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 5.5.31
-
Fix Version/s: 5.5
-
Component/s: None
-
Labels: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 |
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
It could also be possible to use loose index scan in case of equ_ref, if joining only with columns of the aggregate . In that case we can rewrite join to EXISTS or IN subquery and evaluate subquery after the loose scan aggregation.
It save the cost of the join of every row before aggregating , one row cancelled by the join should disable the all group , very similare to subquery cache but applied to group by.
It could also be possible to use index loose scan adding any condition to the query not covered inside the composite index. It should be possible to make ICP works with loose index scan . Jumping to the index , range scan until ICP condition match and repeat over and over . This may get better cost than the full range index scan.
We can imagine 2 cases :
One an index is covering the condition and we may get a cardinality to estimate the cost
No indexes and we should rely on independent statistics