mysql - 如何加速 mysql 结果

Question

我有一个用户传递一个字符串值，我们会说“make”这个词，但遇到了一个问题:

首先，我必须获取搜索字符串的 keyword_id，例如 'make' 具有 keyword_id = 1234。然后，我需要在 15 个不同的表 中查找具有该 keyword_id 的任何记录，并将每个记录的 link_id 与另一个表进行比较并返回所有记录上一个结果中每个 link_id 的该表记录。

我有两个有效的语句，但它们返回结果的速度非常慢。如果发现其他两个条件为真，是否有更好的方法从链接中获取 site_id 和 url ？

这是我的查询:

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword0 AS k0 ON k0.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k0.link_id 
WHERE k.keyword LIKE '%make%' 

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword1 AS k1 ON k1.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k1.link_id 
WHERE k.keyword LIKE '%make%' 

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword2 AS k2 ON k2.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k2.link_id 
WHERE k.keyword LIKE '%make%' 

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword3 AS k3 ON k3.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k3.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword4 AS k4 ON k4.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k4.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword5 AS k5 ON k5.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k5.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword6 AS k6 ON k6.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k6.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword7 AS k7 ON k7.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k7.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword8 AS k8 ON k8.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k8.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword9 AS k9 ON k9.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = k9.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyworda AS ka ON ka.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = ka.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keywordb AS kb ON kb.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = kb.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keywordc AS kc ON kc.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = kc.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keywordd AS kd ON kd.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = kd.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyworde AS ke ON ke.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = ke.link_id 
WHERE k.keyword LIKE '%make%'

UNION ALL

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keywordf AS kf ON kf.keyword_id = k.keyword_id
INNER JOIN search1_links AS l ON l.link_id = kf.link_id 
WHERE k.keyword LIKE '%make%'

但是非常慢 - 任何想法或帮助将不胜感激:

SELECT DISTINCT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM search1_keywords AS k
INNER JOIN search1_link_keyword0 AS k0 ON k0.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword1 AS k1 ON k1.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword2 AS k2 ON k2.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword3 AS k3 ON k3.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword4 AS k4 ON k4.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword5 AS k5 ON k5.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword6 AS k6 ON k6.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword7 AS k7 ON k7.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword8 AS k8 ON k8.keyword_id = k.keyword_id
INNER JOIN search1_link_keyword9 AS k9 ON k9.keyword_id = k.keyword_id
INNER JOIN search1_link_keyworda AS ka ON ka.keyword_id = k.keyword_id
INNER JOIN search1_link_keywordb AS kb ON kb.keyword_id = k.keyword_id
INNER JOIN search1_link_keywordc AS kc ON kc.keyword_id = k.keyword_id
INNER JOIN search1_link_keywordd AS kd ON kd.keyword_id = k.keyword_id
INNER JOIN search1_link_keyworde AS ke ON ke.keyword_id = k.keyword_id
INNER JOIN search1_link_keywordf AS kf ON kf.keyword_id = k.keyword_id
INNER JOIN search1_links AS l 
ON l.link_id = k0.link_id OR
l.link_id = k1.link_id OR
l.link_id = k2.link_id OR
l.link_id = k3.link_id
WHERE k.keyword LIKE '%make%'

任何帮助将不胜感激!

Answer 1

除了疯狂的数据库架构(其中有 15 个不同的关键字表)...

也许，在子查询中，您可以执行关键字表的并集。然后加入该工会一次。连接的成本很高，而且您当前的方法需要对每个关键字表执行多个连接。

SELECT l.link_id, l.url, l.site_id, l.visible, k.keyword_id, k.keyword 
FROM 
     search1_keywords k
     INNER JOIN (
           SELECT keyword_id, link_id FROM search1_link_keyword0
           UNION ALL
           SELECT keyword_id, link_id FROM search1_link_keyword1
           UNION ALL
           ... and on and on
     ) AS keyword ON
           keyword.keyword_id = k.keyword_id
     INNER JOIN search1_links AS l ON l.link_id = keyword.link_id 
WHERE k.keyword LIKE '%make%'