Announcement

Announcement Module
Collapse
No announcement yet.

optimizer and use of indices

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • optimizer and use of indices

    Hi,

    I would like to know how indices are used by the mysql optimizier:

    > select * from mynews where (approved='Y') and (id=52812 or ord=40000)
    is rather slow compared to
    > select * from mynews where (id=52812 or ord=40000) having approved='Y'
    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.
    • 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?
    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:
    > select * from mynews where (approved='Y') and (id=52812 or ord=40000)
    Explain says 44910 rows scanned.
    > select * from mynews where (id=52812 or ord=40000) having approved='Y'
    same result - no wait, instant results
    Explain says 11 rows scanned.
    Different indices make no difference.

  • #2
    The optimizer's use of indices in cases like this is limited. At times you might need to resort to UNION to make the conditions simple enough to be efficient.

    Comment

    Working...
    X