MySQL Bugs: #38248: Order By and Index Drama

Bug #38248	Order By and Index Drama
Submitted:	20 Jul 2008 16:34	Modified:	21 Aug 2008 9:39
Reporter:	Horn Wijaya	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	6.0.3-alpha x86_64	OS:	Linux (Centos5 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	INDEX, no result, order by

Description:
There's a problem we faced for a few months already.
We have a table with 40K rows.
only has 1 primary key

When we do a
select * from table where col1 = 'xxx' and col2 = 1 order by col3
no problem, we got 1 result

But since it's 40K and growing, we add index into col1 and col2
after that , the select statement returns ZERO result

and when we remove the "order by" statement, the 1 result comes out without sorting.

Has been trying to mysql 3, mysql 4, all no such problem, we could index any column we wish, and still be able to search and sort.

Please help us, as we are on production system (replication in place)
Is it because it's alpha?

We have been trying to google this for link between the index and the order by and some mysql configuration variables, but no avail.

Please help us, 8 hours more, operations will resume...

Thanks.

How to repeat:
Everytime, without fail.
Following the description above.

Thank you for the report.

but version 6.0.3 is a bit outdated. Please try with current version 6.0.5 and if you still have same problem provide output of SHOW CREATE TABLE table

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".