Hi

> SELECT DISTINCT * FROM t_apos JOIN t_auftrag on f_id_auftrag
> = id_auftrag WHERE f_liefer_nr LIKE ?
> This code is extremly slow.

Here, the optimizer doen't know anything about the parameter.

> SELECT
> DISTINCT * FROM t_apos JOIN t_auftrag on f_id_auftrag =
> id_auftrag WHERE f_liefer_nr LIKE '2005-0119849-8'
> It is fast (about 50 ms),

Here, the optimizer sees that there is no wildcard in the right-hand operand
of LIKE and this simpilfies the query to:

> SELECT
> DISTINCT * FROM t_apos JOIN t_auftrag on f_id_auftrag =
> id_auftrag WHERE f_liefer_nr = '2005-0119849-8'

So that query can use the index very efficiently.

(In this case the prepared statement is a gotcha. Using a non-prepared
statement would enable the optimizier to do the same in the first case, but
prepared statements are optimized earlier.)

One way to speed up things would be to use = instead of LIKE if possible:

> SELECT DISTINCT * FROM t_apos JOIN t_auftrag on f_id_auftrag
> = id_auftrag WHERE f_liefer_nr = ?

This should be as fast as your second example and as flexible as your first
example as long as you don't use wildcards.

Regards,
Steffen


[Non-text portions of this message have been removed]