这是我的第二个问题，请耐心点：）
我有一个MySQL数据库，其中有一个大表（dati），其结构如下：
CREATE TABLE IF NOT EXISTS `dati` (
`i1` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i2` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i3` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i4` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i5` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i6` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i7` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i8` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i9` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`i10` varchar(200) COLLATE utf8_swedish_ci DEFAULT NULL,
`totale` double DEFAULT NULL,
`valore` double DEFAULT NULL,
KEY `i1` (`i1`(20),`i2`(20),`i3`(20),`i4`(20)),
KEY `i1_2` (`i1`),
KEY `i2` (`i2`),
KEY `i3` (`i3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
还有一些较小的表，其中字段
i1中的值与i4valore中的值通过这种结构链接：CREATE TABLE IF NOT EXISTS `i1` (
`livello` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`valore` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`ordine` double DEFAULT NULL,
`colore` double DEFAULT NULL,
`mostrare` double DEFAULT NULL,
KEY `livello` (`livello`),
KEY `valore` (`valore`),
KEY `ordine` (`ordine`),
KEY `mostrare` (`mostrare`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
CREATE TABLE IF NOT EXISTS `i2` (
`livello` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`valore` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`ordine` double DEFAULT NULL,
`colore` double DEFAULT NULL,
`mostrare` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
CREATE TABLE IF NOT EXISTS `i3` (
`livello` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`valore` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`ordine` double DEFAULT NULL,
`colore` double DEFAULT NULL,
`mostrare` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
CREATE TABLE IF NOT EXISTS `i4` (
`livello` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`valore` varchar(100) COLLATE utf8_swedish_ci DEFAULT NULL,
`ordine` double DEFAULT NULL,
`colore` double DEFAULT NULL,
`mostrare` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
在每一个i1-14之前，我都有一个由用户选择的选项，并与用户对其允许值的权限合并，PHP生成一个查询，格式如下：
SELECT * FROM `dati`
WHERE (`i1` = 'TARGET|TOTALE')
AND (`i2` = 'BANCHE|ADV AWARENESS SPONTANEA OFFLINE')
AND (`i3` = 'BRAND BANCARI|(NET) BANCOPOSTA')
AND (`i4` = 'ANNO|2014' OR `i4` = 'ANNO|2013')
我的问题是，我需要所有键组合的值，甚至是那些在主数据表中没有条目的键组合。
有人建议使用i1-i4表的左连接可以得到所有条目，但我尝试了很多方法，总是得到错误或非常慢或永不结束的查询。如何快速将
data与i<n>-dati连接，以便所有（选定的）键组合都显示出来（即使数据中没有输入）。所以我需要加入dati.i1=i1.valore，dati.i2=i2.valore等等。。
谢谢你的帮助。
（附言：我几个小时内不会听这个，现在是晚上，待会从家里回来）
编辑
现在在家里，我将尝试发布一些可能有帮助的示例。
这是一个客户机脚本的查询，适应了这些表的名称，我不知道他们为什么这样做，它似乎以某种方式工作，虽然它看起来过于复杂，必须用PHP动态生成它：
select k.i4 , k.i3, k.i1, k.i2, dati.valore, dati.totale as totale, s.base
from (( select * from (
select valore as i4, ordine as ordine_i4 from i4 where valore in ('ANNO|2013','ANNO|2014')) p,
(select valore as i3, ordine as ordine_i3 from i3 where valore in ('BRAND BANCARI|(NET) BANCOPOSTA','BRAND BANCARI|(NET) CONTO ARANCIO/ING DIRECT','BRAND BANCARI|(NET) GRUPPO UNICREDIT','BRAND BANCARI|BANCA MEDIOLANUM')) b,
(select valore as i1, ordine as ordine_i1 from i1 where valore in ('TARGET|INTERNAUTI','TARGET|INTERNAUTI SOCIAL')) t,
(select valore as i2, ordine as ordine_i2 from i2 where valore in ('BANCHE|ADV AWARENESS ONLINE TOTALE','BANCHE|BRAND AWARENESS TOM','BANCHE|BRAND AWARENESS TOTALE','BANCHE|BRAND AWARENESS TOTALE SPONTANEA','BANCHE|NOTIZIABILITA\' OFFLINE','BANCHE|NOTIZIABILITA\' OFFLINE + ONLINE','BANCHE|NOTIZIABILITA\' ONLINE')) a
) k left JOIN (
select * from dati where i4 in ('ANNO|2013','ANNO|2014') And i3 in ('BRAND BANCARI|(NET) BANCOPOSTA','BRAND BANCARI|(NET) CONTO ARANCIO/ING DIRECT','BRAND BANCARI|(NET) GRUPPO UNICREDIT','BRAND BANCARI|BANCA MEDIOLANUM') And i1 in ('TARGET|INTERNAUTI','TARGET|INTERNAUTI SOCIAL') And i2 in ('BANCHE|ADV AWARENESS ONLINE TOTALE','BANCHE|BRAND AWARENESS TOM','BANCHE|BRAND AWARENESS TOTALE','BANCHE|BRAND AWARENESS TOTALE SPONTANEA','BANCHE|NOTIZIABILITA\' OFFLINE','BANCHE|NOTIZIABILITA\' OFFLINE + ONLINE','BANCHE|NOTIZIABILITA\' ONLINE')) as dati
on k.i4=dati.i4 and k.i3=dati.i3 and k.i1=dati.i1 and k.i2=dati.i2 ) left JOIN (select i4, i1, i2, valore as base from dati where i1='TOTALE' ) s on k.i4=s.i4 and k.i1=s.i1 and k.i2=s.i2 ORDER BY k.ordine_i3, k.ordine_i4, k.ordine_i2, k.ordine_i1
还有一些我没有工作的试验。这在某个地方有明显的错误，但我似乎无法修复它
((SELECT * FROM `dati`
WHERE (`i1` = 'TARGET|TOTALE')
AND (`i2` = 'BANCHE|ADV AWARENESS SPONTANEA OFFLINE')
AND (`i3` = 'BRAND BANCARI|(NET) BANCOPOSTA')
AND (`i4` = 'ANNO|2014')
) aaa )
LEFT JOIN (
SELECT * FROM (
(SELECT valore AS `vi1`
FROM `i1`
) `ti1`,
(SELECT valore AS `vi2`
FROM `i2`
) `ti2`,
(SELECT valore AS `vi3`
FROM `i3`
) `ti3`,
(SELECT valore AS `vi4`
FROM `i4`
) `ti4`
) bbb
) ON `ti1`.`vi1` = `dati`.`i1` AND `ti2`.`vi2` = `dati`.`i2` AND `ti3`.`vi3` = `dati`.`i3` AND `ti4`.`vi4` = `dati`.`i4`
这个看起来很酷，但却永远锁定MySQL（！!!):
( select * from
( select * from
(select valore as `vi1`,ordine as `oi1` from `i1`) `ti1`,
(select valore as `vi2`,ordine as `oi2` from `i2`) `ti2`,
(select valore as `vi3`,ordine as `oi3` from `i3`) `ti3`,
(select valore as `vi4`,ordine as `oi4` from `i4`) `ti4`
) allkeys
left join
(select * from `dati`
where (`i1`= 'TARGET|TOTALE')
AND (`i2`= 'BANCHE|ADV AWARENESS SPONTANEA OFFLINE')
AND (`i3`= 'BRAND BANCARI|(NET) BANCOPOSTA')
AND (`i4`= 'ANNO|2014') AND (TRUE)
) core
on ( `allkeys`.`vi1`=`core`.`i1` and `allkeys`.`vi2`=`core`.`i2`and `allkeys`.`vi3`=`core`.`i3` and `allkeys`.`vi4`=`core`.`i4` )
)
最佳答案
您可以通过连接来实现所有这些，但是由于您的数据很高，您必须index表以获得更快的查询响应。
可以将索引应用于更经常检索或在联接中反复使用的列。
还要尝试指定选择性列名，而不是*，因为这样会更快地处理列，而不是从所有列中提取数据。
关于php - 快速将大数据表与键表连接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23314679/