MySQL Bugs: #23410: "select * from... where .. order by" very slow when 0 rows match the where cond

Bug #23410	"select * from... where .. order by" very slow when 0 rows match the where cond
Submitted:	18 Oct 2006 7:47	Modified:	13 Mar 2007 19:13
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.26	OS:	Any (*)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	performance

Description:
select keypart1 from table where keypart1 > 0 and keypart2 > 1 order by keypart1 

is magnitudes times faster than

select * from table where keypart1 > 0 and keypart2 > 1 order by keypart1 ASC

The only difference is the * vs keypart1.  However, this query is returning 0 records, so it's got nothing to order by really.

I did a debug trace, and the slow version calls general_fetch where as the fast version does QUICK_RANGE_SELECT's.  Upload are the two explains and debug trace pieces.

How to repeat:
Sorry, no tiny testcase which fits into this window.

1) start mysql server with small enough buffer pool size that won't buffer entire table in memory (else the test is defeated)

2) import the .sql dump (mentioned in the private sections

3) run the two queries

This is a duplicate of case #12113.