python - 每个结果的多个子查询，如何更快？

Question

我正在做这样的查询:

cursor.execute(
    '''
    SELECT person.id, person.name
    FROM person
    JOIN vehicle ON vehicle.owner_id = person.id
    WHERE person.age >= 18 AND vehicle.model = %s
    ''',
    ('Toyota Auris',)
)
people = [dict(row) for row in cursor.fetchall()]

然后我为每个人获取他们的车辆信息:

for person in people:
    cursor.execute(
        '''
        SELECT vehicle.id AS id, vehicle.name AS name, vehicled.model AS model
        FROM vehicle
        JOIN person ON person.id = vehicle.owner_id
        WHERE person.id = %s
        ''',
        (person['id'],)
    )
    person['vehicles'] = [dict(row) for row in cursor.fetchall()]

但是这个取车速度极慢，因为我有超过100万人和100,000辆车注册。有什么方法可以使第二个查询更快？

所以我想要的是拥有与特定型号匹配的车辆的所有 person 对象，以及他们的所有车辆，无论车辆的型号如何。

Answer 1

您可以尝试先获取拥有丰田 Auris 的人，然后对车辆进行双连接。所有这些都适合一个查询。类似的东西

SELECT v1.id, person.id AS pid, v2.id AS vid v2.model AS model FROM vehicle AS v1 JOIN person ON v1.owner_id = person.id JOIN vehicle AS v2 ON v2.owner_id = person.id WHERE person.age >= 18 AND v1.model = 'Toyota Auris';