MySQL - 比子查询更好的方法

I have the following query:

我有以下查询：

SELECT col1, col2, col3 FROM tb1
WHERE col4=ANY(
  SELECT col1 FROM tb2
  WHERE col2=(SELECT col1 FROM tb3 WHERE col3='php generated string')
  AND col3=(SELECT col2 FROM tb3 WHERE col3='same string as above')
);

It works, but it's very slow. I know there is a much better (and faster) way to do this, but my lack of experience with SQL queries means I'm trying to make this harder than it needs to be. I've tried using a JOIN, but I don't truly understand how to make that work in this case either.

它有效，但速度很慢。我知道有一种更好（更快）的方法可以做到这一点，但是我缺乏SQL查询的经验意味着我正在努力使它比它需要的更难。我尝试过使用JOIN，但我也不知道如何在这种情况下完成这项工作。

Any help is much appreciated.

任何帮助深表感谢。

3 个解决方案

#1

You're right that you need to learn how to use JOIN. If you ever are matching up values from one column across multiple tables, you should probably be JOINing the tables together ON that column.

你是对的，你需要学习如何使用JOIN。如果您要在多个表中匹配一列中的值，则应该在该列上将表连接在一起。

SELECT tb1.col1,tb1.col2,tb1.col3 
FROM tb1
JOIN tb2
  ON (tb1.col4 = tb2.col1)
JOIN tb3
  ON (tb1.col2 = tb3.col1
  AND tb1.col3 = tb3.col2)
WHERE tb3.col3 = 'php generated string'

#2

SELECT tb1.col1, tb1.col2, tb1.col3
    FROM tb1
        INNER JOIN tb2
            ON tb1.col4 = tb2.col1
        INNER JOIN tb3
            ON tb1.col2 = tb3.col1
                AND tb3.col3 = 'php generated string'
        INNER JOIN tb3
            ON tb1.col3 = tb3.col2
                AND tb3.col3 = 'same string as above'

#3

If the subquery within ANY can return more than one record, then rewriting your query as a JOIN will result in duplicates.

如果ANY中的子查询可以返回多个记录，那么将查询重写为JOIN将导致重复。

Use this:

用这个：

SELECT  col1, col2, col3
FROM    tb1
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    tb3
        JOIN    tb2
        ON      tb2.col2 = tb3.col1
                AND tb2.col2 = tb3.col2
                AND tb2.col1 = tb1.col4
        WHERE   tb3.col3 = 'php generated string'
        )

and create the following indexes:

并创建以下索引：

tb2 (col1, col2, col3)
tb3 (col3)

#1