Set my indices, query still takes 0.7s

[Jaap Vermeer]

Hi,

The query below takes about 0.7 seconds even after my attempts to rewrite it (using GROUP BY i.s.o. DISTINCT, removing ORDER BY, using OR i.s.o. IN and some minor other tries) and after trying various indices on the tables.

Does anybody have an idea how i could make this faster?

T.i.a.


TABLE1 (2M rows)

mysql> SHOW CREATE TABLE rldal_wi_dokument_fzg;
CREATE TABLE `rldal_wi_dokument_fzg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dokument_id` bigint(20) DEFAULT NULL,
  `vtyp` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `marke` char(1) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mkb` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gkb` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `gtyp` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mj_von` int(11) DEFAULT NULL,
  `mj_bis` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `vm1` (`vtyp`(3),`marke`)
) ENGINE=MyISAM AUTO_INCREMENT=2159859 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

TABLE2: (600k rows)

mysql> SHOW CREATE TABLE rldal_wi_dokument;
CREATE TABLE `rldal_wi_dokument` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dokument_id` bigint(20) DEFAULT NULL,
  `spk` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `og_id` int(11) DEFAULT NULL,
  `bg_id` int(11) DEFAULT NULL,
  `rg_id` int(11) DEFAULT NULL,
  `hkap_id` int(11) DEFAULT NULL,
  `kap_id` int(11) DEFAULT NULL,
  `ukap_id` int(11) DEFAULT NULL,
  `reihenfolge` int(11) DEFAULT NULL,
  `redsystyp` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `l3_dok_id` int(11) DEFAULT NULL,
  `l3_id` int(11) DEFAULT NULL,
  `parent_dokument_id` int(11) DEFAULT NULL,
  `og_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `bg_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `rg_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hkap_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kap_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ukap_bez` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `heft` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dok` (`dokument_id`),
  KEY `lang1` (`spk`(2))
) ENGINE=MyISAM AUTO_INCREMENT=625132 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> EXPLAIN SELECT DISTINCT d.og_id, d.og_bez 
    -> FROM rldal_wi_dokument_fzg f, rldal_wi_dokument d 
    -> WHERE d.spk="de-DE" AND f.marke="N" AND f.vtyp IN ("2K%","2KB") AND f.dokument_id=d.dokument_id 
    -> ORDER BY og_bez;
+----+-------------+-------+-------+---------------+------+---------+-----------------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref                   | rows | Extra                                        |
+----+-------------+-------+-------+---------------+------+---------+-----------------------+------+----------------------------------------------+
|  1 | SIMPLE      | f     | range | vm1           | vm1  | 16      | NULL                  | 7943 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | d     | ref   | dok,lang1     | dok  | 9       | elsaweb.f.dokument_id |    1 | Using where                                  | 
+----+-------------+-------+-------+---------------+------+---------+-----------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)