php - 多表优化查询结果

Question

我正在尝试使用 4 个表获取数据。表格列表

• 数字
• 通话
• ref_label_no
• 标签

我想获取带有特定标签的调用号码的数据。这是表格。
数字表:

select id,uuid,number from numbers limit 1;
+----+--------------------------------------+------------+
| id | uuid                                 | number     |
+----+--------------------------------------+------------+
|  1 | ed268b05-758e-44fd-b429-8d5223651814 | 1234561222 |
+----+--------------------------------------+------------+

调用表

mysql> select id,uuid,did from calls limit 2;
+----+-------------------------------------+------------+
| id | uuid                                | number     |
+----+-------------------------------------+------------+
|  1 | ddddass-b810-4f23-7456-8ff56efab080 | 1234561222 |
|  2 | dddddd-b810-4f23-7456-8ff56efab080  | 123456789  |
+----+-------------------------------------+------------+
2 rows in set (0.02 sec)

ref_label_no 表

mysql> select uuid,number_uuid,label_uuid from ref_label_no limit 1;
+--------------------------------------+--------------------------------------+--------------------------------------+
| uuid                                 | number_uuid                          | label_uuid                           |
+--------------------------------------+--------------------------------------+--------------------------------------+
| 7a5e1b6e-0194-4993-8d78-5f7a1b60c7d2 | ed268b05-758e-44fd-b429-8d5223651814 | 7262b06e-9263-4825-8411-4c107104a60b |
+--------------------------------------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

标签表

mysql> select uuid,name from labels limit 1;
+--------------------------------------+-------+
| uuid                                 | name  |
+--------------------------------------+-------+
| 7262b06e-9263-4825-8411-4c107104a60b | INDIA |
+--------------------------------------+-------+
1 row in set (0.00 sec)

现在我想要像通话次数和总通话次数这样的结果。[我需要这个输出]

mysql> select uuid,name from labels limit 1;
+--------------------------------------+-------+
| Number                       | Total_calls  |
+--------------------------------------+-------+
| 13456789                     |       2      |
+--------------------------------------+-------+
1 row in set (0.00 sec)

我已经进行了以下查询，但没有成功，而且花费了太多时间来响应。

select count(*) as Total_calls,calls.number as did,labels.name,calls.created_at as callstart from calls, labels JOIN ref_label_no ON labels.uuid = ref_label_no.label_uuid JOIN numbers ON numbers.uuid = ref_label_no.number_uuid where labels.name="INDIA"  group by calls.number;

有什么建议或推荐吗？

Answer 1

多个表避免使用连接，使用Union。它会比 join 更快。

像这样，

select uuid,did from calls union 
select uuid,number_uuid as did from ref_label_no union
select uuid,name as did from labels