时间戳

首页 > 解决方案 > 优化 mysql 中具有连接和联合的查询

mysql - 优化 mysql 中具有连接和联合的查询

问题描述

我正在使用 laravel 构建我的网络应用程序。在列出所有订单时，我有一个查询，但在我的本地主机中最多需要 40 秒，而在 Goddady 共享主机中最多需要 95 秒。虽然我实现了服务器端数据表，但查询需要很长时间才能执行。我需要有人建议或帮助使查询更有效地执行。主要有两套，我做了那个联合。

select * from ((
select `subscription`.`id`, 
`subscription`.`order_id`, 
`users`.`name` as `user_name`, 
`subscription`.`new_dated`,
`subscription`.`rescheduling_delivery_date` as expected_date, 
COUNT(`subscription_items`.`product_id`) as items, 
`subscription`.`od_dis_total` as `total`,
`subscription`.`order_discount`, 
`area`.`area_name`,
`subscribe_orders`.`action` as ordertype,
driver.name as driver_name,
'NA' as payment_mode, 
`payment_status`.`status_name` as payment_status,
`subscription_status`.`status_name` as order_status, 
`subscription`.`od_payment_method`,
`subscription_items`.`product_id`, 
subscription.delivered_dated
from `subscription` 
left join `subscription_items` on `subscription`.`id` = `subscription_items`.`subscription_id` 
left join `users` on `subscription`.`user_id` = `users`.`id` 
left join `users` as `driver` on `subscription`.`driver_id` = `driver`.`id` 
left join `ycias_address` on `subscription`.`add_id` = `ycias_address`.`id` 
left join `area` on `ycias_address`.`area_id` = `area`.`id` 
left join `subscribe_order_items` on `subscription`.`subscribe_order_id` = `subscribe_order_items`.`subscribe_order_id` 
left join `subscribe_orders` on `subscription`.`subscribe_order_id` = `subscribe_orders`.`id` 
left join `payment_status` on `subscription`.`payment_status` = `payment_status`.`id` 
left join `subscription_status` on `subscription`.`od_status` = `subscription_status`.`id` 
group by `subscription_items`.`subscription_id`) 
union (
select `orders`.`id`, 
`orders`.`order_id`, 
`users`.`name` as `user_name`, 
`orders`.`new_dated`,
`orders`.`expected_delivery_date` as expected_date, 
COUNT(order_items.product_id) as items,
`orders`.`od_dis_total` as `total`, 
`orders`.`order_discount`, 
`area`.`area_name`, 
'Instant Order' as ordertype, 
driver.name as driver_name, 
orders.payment_mode, 
`payment_status`.`status_name` as payment_status, 
`order_status`.`status_name` as order_status, 
`orders`.`od_payment_method`,
`order_items`.`product_id`, 
`orders`.`delivered_dated` 
from `orders` 
left join `order_items` on `orders`.`id` = `order_items`.`order_id` 
left join `users` on `users`.`id` = `orders`.`user_id` 
left join `users` as `driver` on `driver`.`id` = `orders`.`driver_id` 
left join `ycias_address` on `orders`.`add_id` = `ycias_address`.`id` 
left join `area` on `ycias_address`.`area_id` = `area`.`id` 
left join `payment_status` on `orders`.`payment_status` = `payment_status`.`id` 
left join `order_status` on `orders`.`od_status` = `order_status`.`id` 
group by `order_items`.`order_id`)) as all_orders limit 10 offset 0

标签： mysqljoinoptimizationunion

解决方案

根据Andy Lester给出的建议，我已经为必要的列添加了索引。因此执行时间减少到一半，即localhost 中的18 秒。我进一步调查了索引列的数据类型为VARCHAR。所以我将其更改为INT然后执行，在0.9 secs内得到结果。感谢安迪莱斯特。

推荐阅读