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.