进行有条件的MySQL连接，涉及小表和大表高效

The MySQL query I'm currently trying to perform is functionally equivalent to this:

我目前正在尝试执行的MySQL查询在功能上等同于:

SELECT small_table.A, small_table.B, small_table.C, huge_table.X, huge_table.Y
FROM small_table LEFT JOIN huge_table
ON small_table.D = huge_table.Z
WHERE small_table.E = 'blah'

except that the query doesn't appear to terminate (at least not within a reasonable amount of time), probably because the second table is huge (i.e. 7500 rows with a total size of 3 MB). Can I perform a functionally equivalent join in a reasonable amount of time, or do I need to introduce redundancy by adding columns from the huge table into the small table. (I'm a total beginner to SQL.)

除了查询似乎没有终止(至少不在合理的时间内),可能是因为第二个表很大(即7500行,总大小为3 MB)。我可以在合理的时间内执行功能等效的连接,还是需要通过将巨大的表中的列添加到小表中来引入冗余。 (我是SQL的初学者。)

The clause WHERE small_table.E = 'blah' is static and 'blah' never changes.

子句WHERE small_table.E ='blah'是静态的,'blah'永远不会改变。

Here is the EXPLAIN output as requested:

这是请求的EXPLAIN输出:

Array ( [0] => Array ( [0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => small_table [table] => small_table [3] => ref [type] => ref [4] => E [possible_keys] => E [5] => E [key] => E [6] => 1 [key_len] => 1 [7] => const [ref] => const [8] => 1064 [rows] => 1064 [9] => Using where [Extra] => Using where ) [1] => Array ( [0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => huge_table [table] => huge_table [3] => eq_ref [type] => eq_ref [4] => PRIMARY [possible_keys] => PRIMARY [5] => PRIMARY [key] => PRIMARY [6] => 4 [key_len] => 4 [7] => my_database.small_table.D [ref] => my_database.small_table.D [8] => 1 [rows] => 1 [9] => [Extra] => ) )

数组([0] =>数组([0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => small_table [table] => small_table [3] => ref [type] => ref [4] => E [possible_keys] => E [5] => E [key] => E [6] => 1 [key_len] => 1 [7] => const [ ref] => const [8] => 1064 [rows] => 1064 [9] =>使用where [Extra] =>使用where)[1] =>数组([0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => huge_table [table] => huge_table [3] => eq_ref [type] => eq_ref [4] => PRIMARY [possible_keys] => PRIMARY [ 5] => PRIMARY [key] => PRIMARY [6] => 4 [key_len] => 4 [7] => my_database.small_table.D [ref] => my_database.small_table.D [8] => 1 [ rows] => 1 [9] => [Extra] =>))

1 个解决方案

#1

A few things ...

一些东西 ...

1) Are you executing this query directly in MySQL (either Workbench GUI or command line), or is this query embedded in PHP code? Your EXPLAIN output seems to suggest PHP. If you haven't done so already, try executing the query directly in MySQL and take PHP out of the mix.

1)您是直接在MySQL(Workbench GUI或命令行)中执行此查询,还是嵌入在PHP代码中的此查询?您的EXPLAIN输出似乎建议使用PHP。如果您还没有这样做,请尝试直接在MySQL中执行查询并将PHP从混合中取出。

2) Your EXPLAIN output looks Ok, except I'm wondering about your WHERE clause with small_table.E = 'blah'. The EXPLAIN output shows that there's an index on column E but the key length = 1, which is not consistent to the comparison with 'blah'. What data type did you use for the column definition for small_table.E?

2)你的EXPLAIN输出看起来没问题,除了我想知道你的WHERE子句有small_table.E ='blah'。 EXPLAIN输出显示E列上有一个索引,但密钥长度= 1,这与'blah'的比较不一致。您为small_table.E的列定义使用了哪种数据类型?

3) MySQL is estimating that it needs to scan 1064 rows in small_table. How many total rows are in small_table, and how many do you expect should match this particular query?

3)MySQL估计它需要在small_table中扫描1064行。 small_table中总行数是多少,您期望与此特定查询匹配的行数是多少?

#1