为什么我的查询返回的结果很多? [英] Why is my query returning far to many results?

问题描述

我有一堆应聘者，他们使用某种技能，都有一份或多份工作，每个人在一家公司工作.

I have a bunch of candidates, who have had one or more jobs, each with a company, using some skills.

不良的ascii艺术如下:

Bad ascii art follows:

         ---------------                              ---------------   
         | candidate 1 |                              | candidate 2 |
         --------------- \                             --------------      
           /              \                                 |
       -------              --------                        etc
       |job 1|              | job 2 |  
       -------              ---------  
        /     \              /      \  
  ---------   ---------  ---------   --------  
  |company |  | skills | |company | | skills |  
  ---------   ---------  ---------- ----------  

这是我的数据库:

mysql> describe jobs;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| job_id       | int(11) | NO   | PRI | NULL    | auto_increment |
| candidate_id | int(11) | NO   | MUL | NULL    |                |
| company_id   | int(11) | NO   | MUL | NULL    |                |
| start_date   | date    | NO   | MUL | NULL    |                |
| end_date     | date    | NO   | MUL | NULL    |                |
+--------------+---------+------+-----+---------+----------------+

.

mysql> describe candidates;
+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| candidate_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| candidate_name | char(50) | NO   | MUL | NULL    |                |
| home_city      | char(50) | NO   | MUL | NULL    |                |
+----------------+----------+------+-----+---------+----------------+

.

mysql> describe companies;
+-------------------+---------------+------+-----+---------+----------------+

| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| company_id        | int(11)       | NO   | PRI | NULL    | auto_increment |
| company_name      | char(50)      | NO   | MUL | NULL    |                |
| company_city      | char(50)      | NO   | MUL | NULL    |                |
| company_post_code | char(50)      | NO   |     | NULL    |                |
| latitude          | decimal(11,8) | NO   |     | NULL    |                |
| longitude         | decimal(11,8) | NO   |     | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+

.

mysql> describe skills;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| skill_id | int(11) | NO   | MUL | NULL    |       |
| job_id   | int(11) | NO   | MUL | NULL    |       |
+----------+---------+------+-----+---------+-------+

.

mysql> describe skill_names;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| skill_id   | int(11)  | NO   | PRI | NULL    | auto_increment |
| skill_name | char(32) | NO   | MUL | NULL    |                |
+------------+----------+------+-----+---------+----------------+

这是我的查询尝试(请注意，我打算将通配符更改为字段名称；我只是想使某些功能正常工作):

Here's my attempt at a query (note, I intend to change the wildcards to field names; I am just trying to get something working):

SELECT can.* , co.*, j.*, sn.*
FROM candidates AS can
JOIN jobs AS j 
JOIN companies AS co ON j.company_id = co.company_id
JOIN skills AS s ON s.job_id = j.job_id
JOIN skill_names AS sn ON s.skill_id = s.skill_id

HediSql说/* Affected rows: 0 Found rows: 34,461,651 Warnings: 0 Duration for 1 query: 0.000 sec. (+ 105.078 sec. network) */

查询出了什么问题?我希望从不良的ascii艺术中可以清楚地知道我要达到的目标.

What's wrong with the query? I hope that it is clear from the bad ascii art what I am trying to achieve.

(而且，连接表的顺序是否会造成速度差异?我会担心新的MySql v8函数会在以后将其检索为嵌套JSON)

(also, does it make any speed difference which order I join the tables? I will worry about the new MySql v8 functions which retrieve it as nested JSON later)

推荐答案

您缺少candidates和jobs之间的联接条件，因此在两个表之间都得到了笛卡尔积.另外，skill_names上的连接条件存在问题，其中两列都相同(这再次生成笛卡尔积).

You are missing a join condition between candidates and jobs, so you get a cartesian product between both tables. Also, there is a problem with the join condition on skill_names, where both columns are the same (this again generates a cartesian product).

SELECT can.* , co.*, j.*, sn.*
FROM candidates AS can
JOIN jobs AS j ON j.candidate_id = can.candidate_id --> here: missing join condition
JOIN companies AS co ON j.company_id = co.company_id
JOIN skills AS s ON s.job_id = j.job_id
JOIN skill_names AS sn ON sn.skill_id = s.skill_id  --> and here: wrong join condition

许多RDBMS会在没有ON子句的JOIN上引发语法错误(如果您确实想要笛卡尔积，则需要使用CROSS JOIN对其进行明确说明)，但是，可惜的是，不是MySQL

Many RDBMS would raise a syntax error on a JOIN without an ON clause (if you do want a cartesian product, you need to be explicit about it by using CROSS JOIN), but, alas, not MySQL.

关于这个问题:

我加入桌子的顺序对速度有什么影响?

does it make any speed difference which order I join the tables?

不.只要您使用的是inner join s(而不是left join s)，连接顺序对于查询计划者就无关紧要，这将以它认为效率更高的顺序重新排列它们.

No. As long as you are using inner joins (not left joins), the join order does not matter to the query planner, which will rearrange them in the order which it thinks is the more efficient.

这篇关于为什么我的查询返回的结果很多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！