在具有联合的视图中级联WHERE子句

这还没有解决,但我发现了原因: MySQL View containing UNION does not optimize well...In other words SLOW!

原岗位:

我正在为一个游戏开发数据库。有两张相同的桌子 equipment 和 safety_dep_box . 为了检查玩家是否有设备,我想检查两张桌子。

我不想做两个查询,而是想利用MySQL中的联合功能。我最近才知道我可以创建一个视图。我的观点是:

CREATE VIEW vAllEquip AS SELECT * FROM equipment UNION SELECT * FROM safety_dep_box;

视图创建得很好。但是当我跑步的时候

SELECT * FROM vAllEquip WHERE owner=<id>

查询永远都需要,而独立的选择查询很快。我想我知道原因,但我不知道如何解决。

谢谢!

附加信息:

这两个表在结构上是相同的,但由于它们是1亿行表,所以被拆分了。 该结构包括int id上的主键和int owner上的多个索引。 我不明白的是以下两种情况之间的速度差:

SELECT COUNT(*) FROM (SELECT * FROM equipment WHERE owner=1 UNION ALL SELECT * FROM safety_dep_box WHERE owner=1) AS uES;

0.42秒

SELECT COUNT(*) FROM (SELECT * FROM equipment WHERE owner=1 UNION  SELECT * FROM safety_dep_box WHERE owner=1) AS uES;

0.37秒

SELECT COUNT(*) FROM vAllEquip WHERE owner=1;

60秒后中止

版本:5.1.51

mysql> explain SELECT * FROM equipment UNION SELECT * FROM safety_dep_box;
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+
| id | select_type  | table          | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+
|  1 | PRIMARY      | equipment      | ALL  | NULL          | NULL | NULL    | NULL | 1499148 |       |
|  2 | UNION        | safety_dep_box | ALL  | NULL          | NULL | NULL    | NULL |  867321 |       |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL          | NULL | NULL    | NULL |    NULL |       |
+----+--------------+----------------+------+---------------+------+---------+------+---------+-------+

带有WHERE子句

mysql> explain SELECT * FROM equipment WHERE owner=1 UNION ALL SELECT * FROM safety_dep_box WHERE owner=1
    -> ;
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+
| id | select_type  | table          | type | possible_keys         | key   | key_len | ref   | rows | Extra |
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+
|  1 | PRIMARY      | equipment      | ref  | owner,owner_2,owner_3 | owner | 4       | const |    1 |       |
|  2 | UNION        | safety_dep_box | ref  | owner,owner_3         | owner | 4       | const |    1 |       |
| NULL | UNION RESULT | <union1,2>     | ALL  | NULL                  | NULL  | NULL    | NULL  | NULL |       |
+----+--------------+----------------+------+-----------------------+-------+---------+-------+------+-------+