Subquery klemmt - Indices werden nicht genutzt

[cyrix2k]

Hallo,

ich habe ein mir absolut unverständliches Problem mit Subqueries. Der Aufruf

Code: Select all

'select * from auftraege where kundennr in (1,2,3)'

läuft absolut performant in wenigen ms. Die selbe Abfrage als Subquery in der Form

Code: Select all

'select * from auftraege where kundennr in (select knr from kundenstamm where eintragsdatum like '2006-08-03 10:00:00')

braucht ewig (fast eine halbe Minute).

Die Subquery nur für sich ist ebenfalls in wenigen ms fertig... Weiss jemand warum diese Kombination nicht klappt?

Beide Tabellen sind korrekt indiziert. Bei den Einzelabfragen werden die Indices auch genutzt nur bei dem kombinierten Befehl nicht. Ein EXPLAIN sagt possible keys null... selbst der Paramter force index greift nicht...

Bin für jeden Tipp dankbar. Als Join läuft das ganze auch recht flott ist aber leider nicht praktikabel da die Tabellen sehr groß sind und mehrere Joins nötig wären.

Code: Select all

[mc5000]

Welches DB-System hast Du? MySQL 5.x?

Bei Deiner Angabe ist knr der Tabelle kundenstamm indiziert? Was ist mit eintragsdatum?
Verbessert dies das Laufverhalten?

Code: Select all

select * from auftraege where kundennr in (select distinct knr from kundenstamm where eintragsdatum like '2006-08-03 10:00:00')

Hilft die Angabe von order by knr (sub) und order by kundennr (main)?

Vielleicht nützlich:

MySQL rewrites IN, ALL, ANY, and SOME subqueries in an attempt to take advantage of the possibility that the select-list columns in the subquery are indexed.

MySQL replaces subqueries of the following form with an index-lookup function, which EXPLAIN describes as a special join type (unique_subquery or index_subquery):

... IN (SELECT indexed_column FROM single_table ...)

[cyrix2k]

Hallo mc5000,

danke für die schnelle Antwort! Ja ich habe MySQL 5.0.22 im Einsatz.
Was ich eben leider nicht erwähnt habe, ist das der Index innerhalb der Subquery greift, aber ausserhalb nicht.

Das Explain schreibt bei der Subquery 'using index idx usw...' aber bei der Hauptquery 'using index null' und 'type all'.

Der Index auf der Tabelle auftraege liegt auf kundennr und einem weiteren Feld als zusammengesetzter Schlüssel. Dieser Index wir nur genutzt wenn ich statt der Subquery die Werte direkt eintrage. Dann schreibt Explain 'using index myidx' und type=range aber bei der Subquery ignoriert er ihn.

Ich hätte eigentlich vermutet, dass MySQL die Query von "Innen nach Außen" bearbeitet, also erst die Subquery ausführt und dann die Werte in die eigentliche Query einsetzt und diese erneut ausführt. Aber scheinbar macht es das ja nicht...

Auch order by hat leider nicht geholfen...

[outofbound]

Hi,


Bitte mal die CREATE TABLEs und EXPLAINS posten, sonst wird das zuviel rumgerate. ;)

Gruss,

Out

[cyrix2k]

ja das ist wohl hilfreich

Also hier die CREATES:

Code: Select all

--
-- Table structure for table `auftraege`
--
CREATE TABLE `auftraege` (
  `id` int(4) unsigned NOT NULL,
  `kundennr` int(4) unsigned NOT NULL,
  `counter` int(4) unsigned default NULL,
  UNIQUE KEY `myidx` (`id`,`kundennr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 INDEX DIRECTORY='/var/lib/mykeys/';

--
-- Table structure for table `kundenstamm`
--
CREATE TABLE `kundenstamm` (
  `knr` int(4) unsigned NOT NULL auto_increment,
  `Name` varchar(20) default NULL,
  `Eintragsdatum` timestamp NULL default NULL,
  `status` varchar(20) default NULL,
  PRIMARY KEY  (`knr`),
  KEY `Nachname` (`Nachname`),
  KEY `Eintragsdatum` (`Eintragsdatum`),
  KEY `idx` (`Nachname`,`Eintragsdatum`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

und hier die EXPLAINS:

Code: Select all

mysql> explain select * from auftraege where kundennr in (1,2,3);
+----+-------------+-----------------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table                 | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | auftraege| range | myidx           | myidx  | 4       | NULL | 2560 | Using where | 
+----+-------------+-----------------------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from auftraege where kundennr in (select knr from kundenstamm where eintragsdatum like '2006-08-03 10:00:00');
+----+--------------------+-----------------------+-----------------+-------------------------+---------+---------+------+-----------+--------------------------+
| id | select_type        | table                 | type            | possible_keys           | key     | key_len | ref  | rows      | Extra                    |
+----+--------------------+-----------------------+-----------------+-------------------------+---------+---------+------+-----------+--------------------------+
|  1 | PRIMARY            | auftraege | ALL             | NULL                    | NULL    | NULL    | NULL | 161353676 | Using where              | 
|  2 | DEPENDENT SUBQUERY | kundenstamm       | unique_subquery | PRIMARY,Nachname,Eintragsdatum,idx | PRIMARY | 4       | func |         1 | Using index; Using where | 
+----+--------------------+-----------------------+-----------------+-------------------------+---------+---------+------+-----------+--------------------------+
2 rows in set (0.00 sec)

Wie man sieht wird der Index myidx beim 2.ten Aufruf nicht genutzt...

[mc5000]

Aber myidx wird nicht verwendet...oder :?

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

Du brauchst noch einen Index der kundennr von auftrage als erste Index-Spalte nutzt.

MySQL cannot use a partial index if the columns do not form a leftmost prefix of the index.

Any index that does not span all AND levels in the WHERE clause is not used to optimize the query. In other words, to be able to use an index, a prefix of the index must be used in every AND group.

http://dev.mysql.com/doc/refman/5.1/en/ ... dexes.html

[r. u. serious]

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html

Subquery optimization for IN is not as effective as for the = operator or for IN(value_list) constructs.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN(value_list) construct that lists the same values that the subquery would return.

[isotopp]

Code: Select all

'select * from auftraege where kundennr in (select knr from kundenstamm where eintragsdatum like '2006-08-03 10:00:00')

Code: Select all

select * from auftraege as a join kundenstamm as k on a.kundennur = k.knr where k.eintragsdatum = '2006-08-03 10:00:00'

[cyrix2k]

Hallo, vielen Dank für die Antworten! Jetzt weiss ich wenigstens, dass diese Beschränkung von MySQL ausgeht ;-)

Als Join läuft es wie o.a. für 2 Tabellen auch recht performant, das Problem war nur bei einer anderen Aufgabenstellung der Join von mehr als 2 Tabellen.

Dieses Problem habe ich nun gelöst, indem ich immer nur 2 Tabellen per Join verbinde und die Ergebnisse mit UNION zusammen führe.

Vielen Dank nochmal!