MySQL Bugs: #1843: extremely slow query speeds up when adding apparently unnecessary AND ID>0

Bug #1843	extremely slow query speeds up when adding apparently unnecessary AND ID>0
Submitted:	15 Nov 2003 1:57	Modified:	16 Nov 2003 8:47
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (windows)
Assigned to:		CPU Architecture:	Any

Description:
Hi,

I have the following table:

CREATE TABLE `users` (
  `ID` int(11) NOT NULL auto_increment,
  `Username` varchar(30) NOT NULL default '',
  `Password` varchar(12) NOT NULL default '',
  `URL` varchar(100) default NULL,
  `Email` varchar(50) default NULL,
  `LastIP` varchar(16) default NULL,
  `LastLogin` datetime default '2003-01-01 00:00:00',
  `Logins` int(11) NOT NULL default '0',
  `LastPageLoad` datetime NOT NULL default '2003-01-01 00:00:00',
  `Pages` int(11) NOT NULL default '0',
  `LastRevelation` datetime NOT NULL default '2003-01-01 00:00:00',
  `LocationX` smallint(6) NOT NULL default '0',
  `LocationY` smallint(6) NOT NULL default '0',
  `ImageUploaded` tinyint(1) NOT NULL default '0',
  `ImageAccepted` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`ID`),
  KEY `UsernameIndex` (`Username`),
  KEY `LastLoginIndex` (`LastLogin`),
) TYPE=InnoDB; 

The table has about 70000 rows. The following query is extremely slow (using either MySQLCC or MySQLDriverCS) - it takes about 140 seconds to execute:

SELECT ID, Username, ImageUploaded, ImageAccepted FROM Users WHERE ImageUploaded=1 OR ImageAccepted=1 ORDER BY Username

However, when I change the query to 

SELECT ID, Username, ImageUploaded, ImageAccepted FROM Users WHERE ID>0 AND (ImageUploaded=1 OR ImageAccepted=1) ORDER BY Username

it executes in a fraction of a second, as expected.

How to repeat:
See above.

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Use EXPLAIN and proper indexing to optimize your queries.

Btw. with random data entered in your table I got about the same time execution for both queries - about 1 sec for 26K rows

If you can create a repeatable test case for this strange behaviour, please reopen this bugreport.