CODEWITHSUNDEEP
mysqlindexinggroup-by
Pradeep
Pradeep

Reputation: 665

Mysql group by returns wrong result when using indexes

I have an issue in one of mySql query. Issue is not reproducible on any of our local machines. I have a simple query

SELECT ID 
FROM TABLE_NAME 
WHERE ID IN (15920,15921) 
GROUP BY ID

returns result – 

ID
15920

Which is unexpected result since there is data for both the ids in database.

Using explain command returned the following result for this query 

+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
| id | select_type | table      | type  | possible_keys      | Key                | key_len | Ref | rows | Extra                                 |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+
|  1 | SIMPLE      | TABLE_NAME | range | CUST_SID_SRUN_INDX | CUST_SID_SRUN_INDX |       4 |     |    1 | Using where; Using index for group-by |
+----+-------------+------------+-------+--------------------+--------------------+---------+-----+------+---------------------------------------+

For this issue I have tried following solutions -

• Forcing a derived table – 

SELECT ID
FROM (SELECT ID 
      FROM TABLE_NAME 
      WHERE ID IN (15920,15921)) CUST 
GROUP BY ID

• Using having clause instead of where clause 

SELECT ID 
FROM TABLE_NAME
GROUP BY ID 
HAVING ID IN (15920,15921)

• Ignoring the index used in this table – 

SELECT ID 
FROM TABLE_NAME IGNORE INDEX (CUST_SID_SRUN_INDX)  
WHERE ID IN (15920,15921) 
GROUP BY ID

All the above queries return the expected result as follow :- 

ID
15920
15921

I am trying to analyze the unexpected behavior of group by clause when indexes are used. Please let me know if I could try something else. FYI…The UAT box where the issue occurs is a linux machine with Mysql 5.1.30. The difference that we see is the version of Mysql. We are using Mysql 5.1.52 on our machines. The table which has this issue uses MyISAM databse engine.

Please let me know if any other input is required.

Upvotes: 3

Views: 278

Answers (2)

Pradeep
Pradeep

Reputation: 665

Thanks everyone for your help .

There is a issue in MySql 5.1.30 with MyISAM partitions, after banging my head for several days I have resolved the issue by upgrading MySQL to 5.1.52 version or reorganizing the partitions .

For your reference see the following bugs reported on MySQL forum :

http://bugs.mysql.com/bug.php?id=44821

Upvotes: 1

Martina
Martina

Reputation: 1672

If your query results are wrong using indexes, it is possible that your table is corrupt for some reason. Try:

REPAIR TABLE table_name;

or try backup the data, destroy and re-create the table and repopulate the data into the table from your backup (that will also recreate the indexes)

Visit MySQL Reference Manual - rebuilding tables

Upvotes: 0

Related Questions