Hi,
I would like to know how indices are used by the mysql optimizier:
is rather slow compared to
by a factor of 100.
It seems that the fast index_merge is only used when there are no other conditions in the where clause.
[LIST]
[] What ist the correct approach to optimize complex cases like “OR (b AND C) OR x AND y OR z […]”?
[] If i use combined indices (eg: ord+approved) does it matter if I write the query (ord=20 and approved=‘Y’) or the other way round (approved=‘Y’ and ord=20) Are combined indices considered for OR queries?
[/LIST]
thank you
Manfred
technical background:
I have the folowing Innodb table with ca. 70000 records.
CREATE TABLE mynews (
id int(20) NOT NULL auto_increment,
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
created datetime NOT NULL default ‘0000-00-00 00:00:00’,
changed datetime NOT NULL default ‘0000-00-00 00:00:00’,
ord double(11,0) NOT NULL default ‘0’,
owner varchar(30) collate latin1_german1_ci NOT NULL default ‘’,
sections varchar(255) collate latin1_german1_ci NOT NULL default ‘’,
approved char(1) collate latin1_german1_ci default ‘N’,
title varchar(250) collate latin1_german1_ci NOT NULL default ‘’,
PRIMARY KEY (id),
) ENGINE=InnoDB AUTO_INCREMENT=53418 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PACK_KEYS=1 COMMENT=‘InnoDB free: 10240 kB’
ord is an order column and contains (nearly) unique numbers, id is autoincrement and approved is char(1) where nearly all records contain ‘Y’.
The following query takes quite long:
Explain says 44910 rows scanned.
same result - no wait, instant results
Explain says 11 rows scanned.
Different indices make no difference.