Hi.

One of our mysql servers:
Server version: 5.1.50-rel11.4-log (Percona Server (GPL), 11.4 , Revision 111)
64 bits
OS: GNU/Linux Ubuntu 10.04 Lucid

All tables in our databases are InnoDB.

If we execute this query:

select count(*) from ordenes_compra where b_updated='S' and estado='O';
+----------+
| count(*) |
+----------+
| 0 |
+----------+

mmm... it isn't correct!! We'll try another time:


mysql> select count(id_orden) from ordenes_compra where b_updated='S' and estado='O';
+-----------------+
| count(id_orden) |
+-----------------+
| 1682 |
+-----------------+


Here it is the EXPLAINs for the querys:

mysql> explain select count(id_orden) from ordenes_compra where b_updated='S' and estado='O';
+----+-------------+----------------+------+---------------- -------------+----------------+---------+-------+------+---- ---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+---------------- -------------+----------------+---------+-------+------+---- ---------+
| 1 | SIMPLE | ordenes_compra | ref | sys_c0010069,orden_modified | orden_modified | 6 | const | 4928 | Using where |
+----+-------------+----------------+------+---------------- -------------+----------------+---------+-------+------+---- ---------+



mysql> explain select count(*) from ordenes_compra where b_updated='S' and estado='O';
+----+-------------+----------------+-------------+--------- --------------------+-----------------------------+--------- +------+------+--------------------------------------------- ---------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------------+--------- --------------------+-----------------------------+--------- +------+------+--------------------------------------------- ---------------------------+
| 1 | SIMPLE | ordenes_compra | index_merge | sys_c0010069,orden_modified | orden_modified,sys_c0010069 | 6,12 | NULL | 2404 | Using intersect(orden_modified,sys_c0010069); Using where; Using index |
+----+-------------+----------------+-------------+--------- --------------------+-----------------------------+--------- +------+------+--------------------------------------------- ---------------------------+


But if we apply the IGNORE INDEX clause the results are fine:

mysql> select count(*) from ordenes_compra IGNORE INDEX(sys_c0010069) where b_updated='S' and estado='O';
+----------+
| count(*) |
+----------+
| 1667 |
+----------+

There is a bug 14980 in mysql database (http://bugs.mysql.com/bug.php?id=14980) for this, and another numbered 26331 for the same (http://bugs.mysql.com/bug.php?id=26231).

We are very surprised because these are bugs from three or four years ago.

Please, could you help us with this problem?

Thank you very much.