Bug #3642 <, > or OR with indexes
Submitted: 4 May 2004 0:49 Modified: 4 May 2004 23:25
Reporter: Henrik Wallin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.1.1-alpha OS:Linux (Mandrake Linux 10.0)
Assigned to: CPU Architecture:Any

[4 May 2004 0:49] Henrik Wallin
Description:
Indexes uses too much optimization when it does less than (<), greater than or the OR-operation.

Some examples:

mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete<3) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
| 156300 |        0 |
| 156305 |        0 |
| 156306 |        0 |
| 156309 |        0 |
| 156312 |        0 |
| 156314 |        0 |
| 156320 |        0 |
| 156324 |        0 |
| 156332 |        0 |
| 156335 |        0 |
+--------+----------+
11 rows in set (0,02 sec)
**** No problem here!
mysql> create index rootnr_forumnr_obsolete_index on postings (rootnr, forumnr, obsolete);
mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete<3) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
+--------+----------+
1 row in set (0,00 sec)
***** Hey! Where are my other posts?
mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete=0) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
| 156300 |        0 |
| 156305 |        0 |
| 156306 |        0 |
| 156309 |        0 |
| 156312 |        0 |
| 156314 |        0 |
| 156320 |        0 |
| 156324 |        0 |
| 156332 |        0 |
| 156335 |        0 |
+--------+----------+
11 rows in set (0,00 sec)
****** That works just fine!
mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete=0 or obsolete=1) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
| 156244 |        0 |
+--------+----------+
2 rows in set (0,00 sec)
******  This doesn't work either, but now I get two raws. 

mysql> drop index rootnr_forumnr_obsolete_index on postings;                                               Query OK, 156688 rows affected (14,65 sec)
Records: 156688  Duplicates: 0  Warnings: 0

mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete<3) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
| 156300 |        0 |
| 156305 |        0 |
| 156306 |        0 |
| 156309 |        0 |
| 156312 |        0 |
| 156314 |        0 |
| 156320 |        0 |
| 156324 |        0 |
| 156332 |        0 |
| 156335 |        0 |
+--------+----------+
11 rows in set (0,02 sec)
***** OK, my posts are back!

mysql> create index rootnr_forumnr_nr_index on postings (rootnr, forumnr, nr);
Query OK, 156690 rows affected (12,99 sec)
Records: 156690  Duplicates: 0  Warnings: 0

mysql> select nr, obsolete from postings where  rootnr=156244 and forumnr=2  and (obsolete<3) order by nr limit 0,11;
+--------+----------+
| nr     | obsolete |
+--------+----------+
| 156244 |        0 |
| 156300 |        0 |
| 156305 |        0 |
| 156306 |        0 |
| 156309 |        0 |
| 156312 |        0 |
| 156314 |        0 |
| 156320 |        0 |
| 156324 |        0 |
| 156332 |        0 |
| 156335 |        0 |
+--------+----------+
11 rows in set (0,00 sec)

***** Seems to work well, as long as the 'obsolete' index isn't used. 

How to repeat:
CREATE TABLE `postings` (
  `nr` int(11) NOT NULL auto_increment,
  `forumnr` int(11) NOT NULL default '0',
  `localnr` int(11) NOT NULL default '0',
  `heading` longtext NOT NULL,
  `message` longtext NOT NULL,
  `from` int(11) NOT NULL default '0',
  `sender` varchar(200) NOT NULL default '',
  `created` int(11) NOT NULL default '0',
  `obsolete` int(11) NOT NULL default '0',
  `comments` longtext NOT NULL,
  `commentto` int(11) NOT NULL default '0',
  `rootnr` int(11) NOT NULL default '0',
  PRIMARY KEY  (`nr`),
  KEY `forumnr_index` (`forumnr`),
  KEY `from_index` (`from`),
  KEY `created_index` (`created`),
  KEY `forumnr_obsolete_index` (`forumnr`,`obsolete`),
  KEY `localnr_forumnr_obsolete_index` (`localnr`,`forumnr`,`obsolete`),
  KEY `forumnr_obsolete_localnr_nr_index` (`forumnr`,`obsolete`,`localnr`,`nr`),
  KEY `rootnr_forumnr_obsolete_index` (`rootnr`,`forumnr`,`obsolete`)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

insert into `postings` values (0, 12, 12, 'hed', 'mess', 1,'', 12, 0,
'','',12), 
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 3, '','',1223),
(0, 12, 12, 'hed', 'mess', 1,'', 12, 3, '','',12), 
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 0, '','',1223),
(0, 12, 12, 'hed', 'mess', 1,'', 12, 3,'','',13), 
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 0, '','',1223),
(0, 12, 12, 'hed', 'mess', 1,'', 12, 0, '','',12), 
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 0, '','',1223),
(0, 13, 12, 'hed', 'mess', 1,'', 12, 0, '','',12), 
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 0, '','',1223),
(0, 122, 1223, 'hed', 'mess', 123,'', 2312, 0, '','',1223);

select nr, obsolete from postings where  rootnr=12 and forumnr=12  and (obsolete=0 or obsolete=1) order by nr;

(That returns "Empty set (0,01 sec)" and not 
+-----+----------+
| nr  | obsolete |
+-----+----------+
| 1   |        0 |
| 7   |        0 |
+-----+----------+
as it should or 
  select nr, obsolete from postings where  rootnr=12 and forumnr=12  and
  (obsolete=0) order by nr; 
do.

)

Suggested fix:
Make it return the same this as it does without index, or a "smaller" index.
[4 May 2004 23:25] MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

mysql> select nr, obsolete from postings where  rootnr=12 and forumnr=12  and
    -> (obsolete=0 or obsolete=1) order by nr;
+----+----------+
| nr | obsolete |
+----+----------+
|  1 |        0 |
|  7 |        0 |
+----+----------+
2 rows in set (0.11 sec)

mysql>
mysql> select nr, obsolete from postings where  rootnr=12 and forumnr=12  and
    ->   (obsolete=0) order by nr;
+----+----------+
| nr | obsolete |
+----+----------+
|  1 |        0 |
|  7 |        0 |
+----+----------+
2 rows in set (0.01 sec)

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 4.1.2-alpha-max-log |
+---------------------+
1 row in set (0.00 sec)