这是大查询的一部分,根据发布时的参数动态生成。。。
有一个表user_params
id | user_id | param_id | param_value
--------------------------------------
1 | 5 | 2 | audi
--------------------------------------
2 | 5 | 3 | a4
--------------------------------------
3 | 5 | 4 | silver
--------------------------------------
4 | 7 | 2 | audi
--------------------------------------
5 | 7 | 3 | q3
--------------------------------------
6 | 8 | 2 | ford
--------------------------------------
7 | 8 | 3 | fiesta
--------------------------------------
我想提取所有'user_id',其中'param_id'=1,'param_value'='audi','param_id'=3,'param_value'='a4'
所以在这个例子中,我的结果应该包含id
行1,2,4,5,7。
考虑到这个表有超过5万个条目,最好的方法是什么?
这是一个InnoDB表。
+++现在我看到有一个html表单,其中包含这些表的多选字段。"给我所有驾驶银色奥迪(a4或q3)或大众(passat)的用户(user_id)"。
我正在测试您编写的解决方案。
您必须在同一个表上执行多个联接。
一种方法是像这个
SELECT
t1.user_id
FROM
thetable AS t1,
thetable AS t2,
thetable AS t3
WHERE
t1.user_id = t2.user_id AND t2.user_id=t3.user_id AND
t1.param_id = 1 AND t1.param_value = 'audi' AND
t2.param_id = 3 AND t2.param_value = 'a4' AND
t3.param_id = 4 AND t3.param_value = 'silver'
这相当于使用JOIN
语句,但它只是给出一个想法。
当然,查询必须根据您检查的参数数量进行扩展或减少(示例中为3)。
如果所有字段都建立了索引,那么查询应该在50 K条记录上运行得很快。
好的,我实际上已经根据您在这里发布的示例数据创建了一个测试表,下面是create语句:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`param_id` int(11) DEFAULT NULL,
`param_value` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
接下来,以下是我如何用数据填充它
INSERT INTO `test`
(
`user_id`,
`param_id`,
`param_value`
)
VALUES
(5,2, 'audi'),
(5,3, 'a4'),
(5,4, 'silver'),
(7,2, 'audi'),
(7,3, 'q3'),
(8,2, 'ford'),
(8,3, 'fiesta');
然后,以下是我尝试过的各种查询:
/** using OR + DISTINCT **/
SELECT DISTINCT user_id
FROM test
WHERE (param_id = 1 AND param_value = 'audi')
OR (param_id = 3 AND param_value = 'a4')
OR (param_id = 4 AND param_value = 'silver')
/** using OR + GROUP BY **/
SELECT user_id
FROM test
WHERE (param_id = 1 AND param_value = 'audi')
OR (param_id = 3 AND param_value = 'a4')
OR (param_id = 4 AND param_value = 'silver')
GROUP BY user_id
/** Using tripple join **/
SELECT
t1.user_id
FROM
test AS t1,
test AS t2,
test AS t3
WHERE
t1.user_id = t2.user_id AND t2.user_id=t3.user_id AND
t1.param_id = 2 AND t1.param_value = 'audi' AND
t2.param_id = 3 AND t2.param_value = 'a4' AND
t3.param_id = 4 AND t3.param_value = 'silver'
/** JOIN with ON clause instead of WHERE **/
SELECT
t1.user_id
FROM
test AS t1
JOIN
test AS t2
ON t1.user_id = t2.user_id
JOIN
test AS t3
ON t3.user_id = t1.user_id
WHERE
t1.param_id = 2 AND t1.param_value = 'audi' AND
t2.param_id = 3 AND t2.param_value = 'a4' AND
t3.param_id = 4 AND t3.param_value = 'silver'
所有这些查询都会产生相同的结果。然而,获得结果的方式确实非常不同,这显示在EXPLAIN EXTENDED
输出中,您现在可以通过运行所有这些查询和准备EXPLAIN EXTENDED
来轻松地看到它,因为我太懒了,无法将所有输出粘贴到ATM
您应该看到的是,JOIN
查询都显示为联接类型ALL
,这意味着整个表被扫描(3次),rows
计数反映了这一点,每个join都会影响所有7行(因此总共21行)
与无JOIN
的查询相比,后者只需要扫描7行,我认为在这种情况下,联接很可能不是答案。
如果您想加快速度,可以向受影响的列添加索引。但实际上,任何DBM都不应该有50k数据集的问题。没那么多。
好吧,术语'param_id' = 1 AND 'param_value' = 'audi' AND 'param_id' = 3 AND 'param_value' = 'a4'
总是假的,所以我只是假设你想使用(param_id = 1 AND param_value = 'audi') OR (param_id = 3 AND 'param_value' = 'a4')
在这种情况下,查询可能类似于:select distinct user_id where (param_id = 1 AND param_value = 'audi') OR (param_id = 3 AND 'param_value' = 'a4')
如果每次都用AND尝试,则无法获得任何值。它所做的是,他试图获得一个参数id为1、值为audi、参数id为3、参数值为a4的user_id。你需要用OR来尝试,我看不到任何param_id为1,所以请确保存在一个。如果我正确理解你的问题,你需要做一些类似的事情
SELECT user_id FROM tablename WHERE 'param_id' = 1 AND 'param_value' = 'audi' OR 'param_id' = 3 AND 'param_value' = 'a4'
请尝试查询中的以下where条件
where param_id in (1,3) && param_value in ('audi','a4')