Elena Stepanova added a comment - 2013-04-23 20:44 - edited Did you try to run ANALYZE TABLE after inserting the data? Actually, the problem is universal and happens on MySQL just as well, due to the fact that after populating a table the statistics is usually far off: .... mysql> insert ignore into gb1(dt1,nr) select dt1 - interval rand() * 10000 second, rand()*1000 from gb1; Query OK, 511820 rows affected (33.62 sec) Records: 519864 Duplicates: 8044 Warnings: 0 mysql> explain select max(dt1) from gb1 group by nr; --- ----------- ----- ----- ------------- ------- ------- ---- ------- ------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ----- ------------- ------- ------- ---- ------- ------------+ 1 SIMPLE gb1 index NULL PRIMARY 12 NULL 1106431 Using index --- ----------- ----- ----- ------------- ------- ------- ---- ------- ------------+ 1 row in set (0.00 sec) mysql> show index in gb1; ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- gb1 0 PRIMARY 1 nr A 1130824 NULL NULL   BTREE     gb1 0 PRIMARY 2 dt1 A 1130824 NULL NULL   BTREE     ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- 2 rows in set (0.00 sec) mysql> analyze table gb1; --------- ------- -------- ---------+ Table Op Msg_type Msg_text --------- ------- -------- ---------+ test.gb1 analyze status OK --------- ------- -------- ---------+ 1 row in set (0.00 sec) mysql> explain select max(dt1) from gb1 group by nr; --- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra --- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ 1 SIMPLE gb1 range NULL PRIMARY 4 NULL 2175 Using index for group-by --- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ 1 row in set (0.00 sec) mysql> select @@version, @@version_comment; ---------- -----------------------------+ @@version @@version_comment ---------- -----------------------------+ 5.5.30 MySQL Community Server (GPL) ---------- -----------------------------+ 1 row in set (0.00 sec) ... and the same picture on MariaDB 5.5: MariaDB [test] > insert ignore into gb1(dt1,nr) select dt1 - interval rand() * 10000 second, rand()*1000 from gb1; Query OK, 511786 rows affected, 8063 warnings (32.66 sec) Records: 519849 Duplicates: 8063 Warnings: 8063 MariaDB [test] > MariaDB [test] > MariaDB [test] > explain select max(dt1) from gb1 group by nr; ----- ----------- ----- ----- ------------- ------- ------- ---- ------ ------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ------- ------- ---- ------ ------------+ 1 SIMPLE gb1 index NULL PRIMARY 12 NULL 954331 Using index ----- ----------- ----- ----- ------------- ------- ------- ---- ------ ------------+ 1 row in set (0.00 sec) MariaDB [test] > show index in gb1; ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- gb1 0 PRIMARY 1 nr A 985088 NULL NULL   BTREE     gb1 0 PRIMARY 2 dt1 A 985088 NULL NULL   BTREE     ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- 2 rows in set (0.01 sec) MariaDB [test] > analyze table gb1; --------- ------- -------- ---------+ Table Op Msg_type Msg_text --------- ------- -------- ---------+ test.gb1 analyze status OK --------- ------- -------- ---------+ 1 row in set (0.00 sec) MariaDB [test] > show index in gb1; ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- gb1 0 PRIMARY 1 nr A 3000 NULL NULL   BTREE     gb1 0 PRIMARY 2 dt1 A 1005177 NULL NULL   BTREE     ------ ---------- -------- ------------ ----------- --------- ----------- -------- ------ ---- ---------- ------- -------------- 2 rows in set (0.00 sec) MariaDB [test] > explain select max(dt1) from gb1 group by nr; ----- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ id select_type table type possible_keys key key_len ref rows Extra ----- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ 1 SIMPLE gb1 range NULL PRIMARY 4 NULL 3001 Using index for group-by ----- ----------- ----- ----- ------------- ------- ------- ---- ---- -------------------------+ 1 row in set (0.00 sec) MariaDB [test] > select @@version, @@version_comment; --------------- ------------------+ @@version @@version_comment --------------- ------------------+ 5.5.30-MariaDB MariaDB Server --------------- ------------------+ 1 row in set (0.00 sec) You might not encountered it on MySQL because you previously played with the table, or just because you were lucky, as the stats are not deterministic (unless you are using persistent statistics, e.g. engine-independent statistics in MariaDB 10.0.1+: https://kb.askmonty.org/en/engine-independent-table-statistics )

Elena Stepanova added a comment - 2013-04-30 23:19 I hope the above explains your observations. If it doesn't, please add a comment and we'll re-open the report if necessary.