从两个mysql表中查找字谜

我目前正在尝试实现一种算法来查找看起来像真实姓名的字谜。我有一个有效的解决方案，但在某些查询上花费了太多时间，我想知道如何改进它。

我试图根据包含 50k 名和 50k 姓氏的数据库查找由名和姓组成的字谜。数据库的架构如下：

CREATE TABLE `forename` (
  `id` int(11) NOT NULL,
  `q` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `labels` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `labels_length` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `surname` (
  `id` int(11) NOT NULL,
  `q` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `labels` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `labels_length` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `forename`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_length` (`labels_length`);
ALTER TABLE `forename` ADD FULLTEXT KEY `idx_labels` (`labels`);

ALTER TABLE `surname`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_length` (`labels_length`),
  ADD KEY `idx_labels` (`labels`);

在每个表中，列的含义如下：

• label : 名或确定名
• labels:标签的slugified版本 : 所有大写字符按字母顺序排序；
• labels_length: 中的字符数labels；

我目前正在使用在 php 中生成的查询来查询这个数据库，例如，对于 Ada Lovelace，它看起来像：

select distinct A.label as surname, B.label as forename 
from forename as A, surname as B WHERE (A.labels not like '%B%' and B.labels not like '%B%') AND 
(A.labels not like '%F%' and B.labels not like '%F%') AND 
(A.labels not like '%G%' and B.labels not like '%G%') AND 
(A.labels not like '%H%' and B.labels not like '%H%') AND 
(A.labels not like '%I%' and B.labels not like '%I%') AND 
(A.labels not like '%J%' and B.labels not like '%J%') AND 
(A.labels not like '%K%' and B.labels not like '%K%') AND 
(A.labels not like '%M%' and B.labels not like '%M%') AND 
(A.labels not like '%N%' and B.labels not like '%N%') AND 
(A.labels not like '%P%' and B.labels not like '%P%') AND 
(A.labels not like '%Q%' and B.labels not like '%Q%') AND 
(A.labels not like '%R%' and B.labels not like '%R%') AND 
(A.labels not like '%S%' and B.labels not like '%S%') AND 
(A.labels not like '%T%' and B.labels not like '%T%') AND 
(A.labels not like '%U%' and B.labels not like '%U%') AND 
(A.labels not like '%W%' and B.labels not like '%W%') AND 
(A.labels not like '%X%' and B.labels not like '%X%') AND 
(A.labels not like '%Y%' and B.labels not like '%Y%') AND 
(A.labels not like '%Z%' and B.labels not like '%Z%') AND 
(A.labels like '%A%' or B.labels like '%A%') AND 
(A.labels like '%C%' or B.labels like '%C%') AND 
(A.labels like '%D%' or B.labels like '%D%') AND 
(A.labels like '%E%' or B.labels like '%E%') AND 
(A.labels like '%L%' or B.labels like '%L%') AND 
(A.labels like '%O%' or B.labels like '%O%') AND 
(A.labels like '%V%' or B.labels like '%V%') AND 
(A.labels_length + B.labels_length) = 11

这个查询的解释是 Ada Lovelace slugAAACDEELLOV所以我需要找到包含这些字母并且不包含字母表中其他字母的姓氏和名字。我在字符数上添加了一个过滤器，以尝试限制返回的行数。

通过这个查询，我得到了需要使用 PHP 处理的结果，以控制每个字符的使用次数是否正确（例如，对于 Ada Lovelace，我的结果包含 3 A）。

我当前的数据库包含大约 50k 个姓氏和 50k 个名字。当我搜索 Ada Lovelace 时，我在大约 0.30 秒内得到 458 个 SQL 行（如果你想知道的话，可以找到 11 个精确的字谜）。

如果我更改对 Sylvain Lovelace 的搜索，我会在 10 多秒内得到 1774 行。慢了 30 倍，Ada Lovelace 可以接受的持续时间现在超出了范围。我试图删除对字符数的过滤器，并且持续时间降低到 8 秒，仍然太多了。

我很确定应该可以改进我的数据库的索引，或者我的查询的构建方式。如果有人有任何想法，我很乐意尝试！

如果有人想在真实数据上尝试，转储可在 github 存储库中获得。