Strange behaviour of JOIN

Question

Hi all,

I have a strange problem with my query trying to join 3 tables. The tables descibed:

2 similar tables like this (entr_es):

Field   Type    Null    Key Default Extra  
espid   int(11) NO  PRI NULL    auto_increment 
haslo   text    NO  MUL NULL   
kat int(11) NO  NULL   

The second table looks the same, except the first column whis is named polid.

The third table is:

Field   Type    Null    Key Default Extra
polid   int(11) NO  PRI NULL
espid   int(11) NO  PRI NULL

And here are the queries:

The first one:

SELECT entr_pl.haslo AS srchaslo, entr_es.haslo AS trghaslo
FROM entr_es, entr_pl, rel_pl_es
WHERE entr_pl.polid=rel_pl_es.polid
AND rel_pl_es.espid=entr_es.espid
AND entr_pl.haslo LIKE "%ludzk%"

works fast, about 0.2 sec. which is enough for me.

But the other one, which is nearly the same, but inverse:

SELECT entr_es.haslo AS srchaslo, entr_pl.haslo AS trghaslo
FROM entr_es, entr_pl, rel_pl_es
WHERE entr_pl.polid=rel_pl_es.polid
AND rel_pl_es.espid=entr_es.espid
AND entr_es.haslo LIKE "%hum%"

works bad - the results are OK, but the query takes about 2.2 to execute and I dont know why. I looked at the EXPLAIN, but the only thing I found is that the second query, the slow one, is "using temporary". I don't know why. I'm sure there is something obvious that I can't see, maybe there is also a way to simplify these queries.

Please help and great thanks in advance, Best regards,

Kamil

=======

I'm terribly sorry - I went throught it once again and the slowdown problem is elsewhere.

It's the

ORDER BY srchaslo

clause on the end of each query. I ommited it in my first post thinking it's not relevant, but it is. It works fine and fast on the first query, but slows down terribly on the other one. I tried to add the COLLATE statement followed by different encodings, but it doesn't work. Then I tried to wrap this query into SELECT * FROM ( query ) AS t1 and throw the ORDER BY outside the (), but this wont work neither. The query is as slow as it was. I don't have any ideas how to fix this.

Answer 1

You need an extra index on rel_pl_es:

create index idx2 on rel_pl_es( espid,polid);

For the second query, the existing index does not work.