开发者

a simple indexed join on two tables taking ages

I'm trying to do the following select:

select `table1`.`index2`
    from `table1`, `table2`
    where `table1`.`index1` = `table2`.`index1`
        and `table1`.`index2` != `table2`.`index2`

index1 and index2 are varchar(255) and are indexed. both tables conta开发者_如何学运维in about 50k rows.

This query took 10 mins and I killed it because its way too long.

Why is it taking so long?


Is this query has best performance ? Using left outer join or inner join sometimes improve the query.

select `table1`.`index2`
from `table1`
inner join table2 on `table1`.`index1` = `table2`.`index1`
    and `table1`.`index2` != `table2`.`index2`


Found the problem, has nothing to do with the query.
Look like creating the index like

index(`index1`, `index2`)

creates some sort of stupid index, but creating it like

index(`index1`),
index(`index2`)

fixed my problems. what is the first one doing?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜