联接列上包含where的慢速查询

Question

我尝试使用JOIN-将2个查询合并为1个

// Query 1
$entity = DB::select("
SELECT memo_id 
FROM entities 
WHERE entity_type = 4 
AND entity_id = '".$entity_nr."' 
LIMIT 1
");
$memo = DB::select("
SELECT memo 
FROM memos 
WHERE id = '".$entity[0]->memo_id."' 
LIMIT 1
");
// Query 2
$memo = DB::select('
SELECT memo
FROM memos 
JOIN entities ON "memos"."id" = "entities"."memo_id" 
WHERE "entities"."entity_type" = 4
AND "entities"."entity_id" IN (?)
LIMIT 1
', [$entity_nr]);

Query 1的2个查询在不到一秒钟的时间内完成。Query 2需要几秒钟的时间。如果我删除Query 2的where子句，它执行得很快。使用AND "entities"."entity_id" = ?也没有帮助。

如何解决这个问题？

更新2020-09-27

重构我的问题。希望这能让事情变得更清楚。预期的输出是获取备忘录。总是只有一个可能的匹配。在示例代码中，我没有检查结果的存在，因为它并没有真正为问题添加一些内容。

我使用的数据库是"；Actian Zen数据库"；。

表格结构

//////////////////////////
// Table: "entity_memo" //
//////////////////////////
CREATE TABLE "entity_memo" (
"entity_type" SMALLINT,
"entity_id" CHAR(15),
"memo_id" INTEGER
);
CREATE UNIQUE INDEX "key0" ON "entity_memo" ( "entity_type", "entity_id", "memo_id" );
CREATE INDEX "key1" ON "entity_memo" ( "memo_id", "entity_type", "entity_id" );
//////////////////////////
// Table: "memos"       //
//////////////////////////
CREATE TABLE "memos" (
"id" INTEGER,
"memo" LVAR(32002) 
);
CREATE UNIQUE INDEX "key0" ON "memos" ( "id" );

选项1(=快速，但有2个查询(

$entity_type = 1;
$entity_id = 'ABC123456';
$entity = DB::select('
SELECT memo_id
FROM entity_memo 
WHERE entity_type = ? 
AND entity_id = ? 
LIMIT 1
', [$entity_type, $entity_id]
);
$memo = DB::select('
SELECT memo
FROM memos 
WHERE id = ? 
LIMIT 1
', [$entity[0]->memo_id]
);
return $memo[0]->memo;

选项2(=慢速，但仅1个查询(

$entity_type = 1;
$entity_id = 'ABC123456';
$memo = DB::select('
SELECT memo
FROM memos 
JOIN entity_memo ON "memos"."id" = "entity_memo"."memo_id" 
WHERE "entity_memo"."entity_type" = ?
AND "entity_memo"."entity_id" = ?
LIMIT 1
', [$entity_type, $entity_id]
);
return $memo[0]->memo;

Answer 1

如果不使用JOIN编写，它看起来会更简单。

SELECT ent.unique_id, mem.memo
FROM entities ent, memos mem
WHERE ent.unique_id = mem.unique_id
AND ent.entity_type = 4
AND ent.entity_id = '".$entity_nr."'

但查询工作缓慢的原因在于表本身。您应该为PK和FK列添加索引，以便查询工作得更快。