我有一个通过PHP框架核心函数生成的查询。 我无法控制更改查询。 因此,我需要在服务器端执行优化,即mysql,以便在有效的时间内执行此查询。 我已经应用了一些指数,但它仍需要4-5秒,理想情况下需要1-1.5秒。 以下是查询:
(
SELECT rr.rt_bids_aos_quotes_relaos_quotes_idb AS so_id,
rr.sales_order_sequence sequence,
so.*
FROM rt_bids_aos_quotes_rel AS rr
INNER JOIN aos_quotes AS so ON so.id = rr.rt_bids_aos_quotes_relaos_quotes_idb
WHERE rr.deleted = 0
AND rr.rt_bids_aos_quotes_relrt_bids_ida='490395-403600-b'
)
UNION
(
SELECT ra.rt_bids_aos_quotes_altaos_quotes_idb AS so_id,
'' AS sequence,
so.*
FROM rt_bids_aos_quotes_alternate AS ra
INNER JOIN aos_quotes AS so ON so.id = ra.rt_bids_aos_quotes_altaos_quotes_idb
WHERE ra.deleted = 0
AND ra.rt_bids_aos_quotes_altrt_bids_ida='490395-403600-b'
)
以下是Explain查询结果: Image_here
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY rt_bids_aos_quotes_rel const idx_rt_bids_aos_quotes_relrt_bids_ida_deleted,rt_bids_aos_quotes_rel_alt,idx_rt_bids_aos_quotes_rel_rt_bids_aos_quotes_relaos_quotes_idb idx_rt_bids_aos_quotes_relrt_bids_ida_deleted 113 const,const 1 NULL
1 PRIMARY so ALL NULL NULL NULL NULL 631950 Using where
2 UNION NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
NULL UNION RESULT ALL NULL NULL NULL NULL NULL Using temporary
显示创建表结果:
CREATE TABLE `rt_bids_aos_quotes_rel` (
`id` varchar(36) NOT NULL,
`date_modified` datetime DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
`rt_bids_aos_quotes_relrt_bids_ida` varchar(36) DEFAULT NULL,
`rt_bids_aos_quotes_relaos_quotes_idb` varchar(36) DEFAULT NULL,
`sales_order_sequence` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_rt_bids_aos_quotes_relrt_bids_ida_deleted` (`deleted`,`rt_bids_aos_quotes_relrt_bids_ida`),
KEY `rt_bids_aos_quotes_rel_alt` (`rt_bids_aos_quotes_relrt_bids_ida`,`rt_bids_aos_quotes_relaos_quotes_idb`),
KEY `idx_rt_bids_aos_quotes_rel_rt_bids_aos_quotes_relaos_quotes_idb` (`rt_bids_aos_quotes_relaos_quotes_idb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `aos_quotes` (
`id` char(36) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`date_entered` datetime DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`modified_user_id` char(36) DEFAULT NULL,
`created_by` char(36) DEFAULT NULL,
`description` text,
`deleted` tinyint(1) DEFAULT '0',
`assigned_user_id` char(36) DEFAULT NULL,
`approval_issue` text,
`billing_account_id` char(36) DEFAULT NULL,
`billing_contact_id` char(36) DEFAULT NULL,
`billing_address_street` varchar(150) DEFAULT NULL,
`billing_address_city` varchar(100) DEFAULT NULL,
`billing_address_state` varchar(100) DEFAULT NULL,
`billing_address_postalcode` varchar(20) DEFAULT NULL,
`billing_address_country` varchar(255) DEFAULT NULL,
`shipping_address_street` varchar(150) DEFAULT NULL,
`shipping_address_city` varchar(100) DEFAULT NULL,
`shipping_address_state` varchar(100) DEFAULT NULL,
`shipping_address_postalcode` varchar(20) DEFAULT NULL,
`shipping_address_country` varchar(255) DEFAULT NULL,
`expiration` date DEFAULT NULL,
`number` int(11) NOT NULL,
`opportunity_id` char(36) DEFAULT NULL,
`template_ddown_c` text,
`total_amt` decimal(26,6) DEFAULT NULL,
`total_amt_usdollar` decimal(26,6) DEFAULT NULL,
`subtotal_amount` decimal(26,6) DEFAULT NULL,
`subtotal_amount_usdollar` decimal(26,6) DEFAULT NULL,
`discount_amount` decimal(26,6) DEFAULT NULL,
`discount_amount_usdollar` decimal(26,6) DEFAULT NULL,
`tax_amount` decimal(26,6) DEFAULT NULL,
`tax_amount_usdollar` decimal(26,6) DEFAULT NULL,
`shipping_amount` decimal(26,6) DEFAULT NULL,
`shipping_amount_usdollar` decimal(26,6) DEFAULT NULL,
`shipping_tax` varchar(100) DEFAULT NULL,
`shipping_tax_amt` decimal(26,6) DEFAULT NULL,
`shipping_tax_amt_usdollar` decimal(26,6) DEFAULT NULL,
`total_amount` decimal(26,6) DEFAULT NULL,
`total_amount_usdollar` decimal(26,6) DEFAULT NULL,
`currency_id` char(36) DEFAULT NULL,
`stage` varchar(100) DEFAULT 'Draft',
`term` varchar(100) DEFAULT NULL,
`terms_c` text,
`approval_status` varchar(100) DEFAULT NULL,
`invoice_status` varchar(100) DEFAULT 'Not Invoiced',
`subtotal_tax_amount` decimal(26,6) DEFAULT NULL,
`subtotal_tax_amount_usdollar` decimal(26,6) DEFAULT NULL,
`bid_id` char(36) DEFAULT NULL,
`alt` varchar(255) DEFAULT NULL,
`group_desc` longtext,
`hold` tinyint(1) DEFAULT '0',
`order_amount` decimal(26,2) DEFAULT NULL,
`order_description` longtext,
`status` varchar(100) DEFAULT NULL,
`type` varchar(255) DEFAULT NULL,
`contract_id` char(36) DEFAULT NULL,
`customer_discount` decimal(26,2) DEFAULT NULL,
`customer_markup` decimal(26,2) DEFAULT NULL,
`markup_inv_type` decimal(26,2) DEFAULT NULL,
`location_id` char(36) DEFAULT NULL,
`active` varchar(100) DEFAULT 'Active',
`city` varchar(255) DEFAULT NULL,
`rt_jobs_id` char(36) DEFAULT NULL,
`system_type` varchar(36) DEFAULT NULL,
`com_address` varchar(255) DEFAULT NULL,
`com_city` varchar(255) DEFAULT NULL,
`com_mapsco` varchar(255) DEFAULT NULL,
`com_state_zip` varchar(255) DEFAULT NULL,
`job_type` varchar(100) DEFAULT NULL,
`calc_labor` varchar(100) DEFAULT 'Item_Install_amt',
`comission` decimal(10,4) DEFAULT '0.0000',
`hourly_labor_rate` decimal(8,4) DEFAULT NULL,
`labor_percentage_of_price` decimal(12,2) DEFAULT NULL,
`overhead` decimal(12,4) DEFAULT '0.0000',
`profit` decimal(12,4) DEFAULT '0.0000',
`pricing_checkbox` tinyint(1) DEFAULT '0',
`pkg_package_id` char(36) DEFAULT NULL,
`dh_factor` decimal(6,2) DEFAULT '0.00',
`addl_builder_price` decimal(12,4) DEFAULT '0.0000',
`billing_notes` longtext,
`billing_notes_home` longtext,
`builder_percentage` decimal(12,4) DEFAULT '0.0000',
`discount` decimal(12,4) DEFAULT '0.0000',
`jobs_contact_homeowner_id` char(36) DEFAULT NULL,
`mortage` varchar(100) DEFAULT 'mortage',
`oh_percentage` decimal(12,2) DEFAULT '0.00',
`origin` varchar(255) DEFAULT NULL,
`package_items` decimal(10,2) DEFAULT NULL,
`package_items_unit_left` decimal(10,2) DEFAULT '0.00',
`selected_package_units` decimal(10,2) DEFAULT '0.00',
`jobs_account_superintendent_id` char(36) DEFAULT NULL,
`subdivision_selected_id` char(36) DEFAULT NULL,
`subdivision_selected_name` varchar(255) DEFAULT NULL,
`department` varchar(255) DEFAULT 'Dallas',
`locked` tinyint(1) DEFAULT '0',
`billed_amount` decimal(26,2) DEFAULT '0.00',
`billed_percentage` decimal(26,2) DEFAULT '0.00',
`retained_amount` decimal(26,2) DEFAULT '0.00',
`selected_package_amount` decimal(26,2) DEFAULT '0.00',
`builder_amount` decimal(26,2) DEFAULT '0.00',
`home_owner_amount` decimal(26,2) DEFAULT '0.00',
`builderContractAmount` decimal(26,2) DEFAULT '0.00',
`homeOwnerContractAmount` decimal(26,2) DEFAULT '0.00',
`ho_pct` decimal(26,6) DEFAULT '0.000000',
`builder_pct` decimal(26,6) DEFAULT '0.000000',
`labor_pct` decimal(26,6) DEFAULT '0.000000',
`mortgage` tinyint(1) DEFAULT '0',
`ho_order` tinyint(1) DEFAULT '0',
`home_owner_sale_order` tinyint(1) DEFAULT '0',
`directly_created_contract` tinyint(1) DEFAULT '0',
`crew_manager_id` char(36) DEFAULT NULL,
`estimate_date` date DEFAULT NULL,
`complete` tinyint(1) DEFAULT '0',
`complete_note` text,
`plan_estimate_date` date DEFAULT NULL,
`plan_manager_id` char(36) DEFAULT NULL,
`lock_bid_id` char(36) DEFAULT NULL,
`documents_id` text,
`no_item_change` tinyint(1) DEFAULT '0',
`tagged_at_yard` tinyint(1) DEFAULT '0',
`soap_created_so` tinyint(1) DEFAULT '0',
`soap_created_so_amount` decimal(10,2) DEFAULT '0.00',
`subcon_vendor` varchar(255) DEFAULT NULL,
`plan_complete` varchar(100) DEFAULT 'In Progress',
`plan_required` tinyint(1) DEFAULT '0',
`plan_important` varchar(255) DEFAULT '0',
`confirm` varchar(100) DEFAULT 'Unconfirm',
`designer_notes` text,
`plan_due_date` date DEFAULT NULL,
`plan_required_so` tinyint(1) DEFAULT '0',
`plan_request` tinyint(1) DEFAULT '0',
`material_hold_status` varchar(255) DEFAULT NULL,
`wh_warehouse_id` char(36) DEFAULT NULL,
`work_order_created` tinyint(1) DEFAULT '0',
`maintenance_status` varchar(100) DEFAULT NULL,
`classification_type` varchar(255) DEFAULT NULL,
`pricing_type` varchar(255) DEFAULT NULL,
`estimate_end_date` date DEFAULT NULL,
`install_date` date DEFAULT NULL,
`is_matched` tinyint(1) DEFAULT '0',
`builder_discount` decimal(10,2) DEFAULT '0.00',
`ho_discount` decimal(10,2) DEFAULT '0.00',
`locate_required` tinyint(1) DEFAULT '0',
`priority` varchar(100) DEFAULT NULL,
`is_no_charged` tinyint(1) DEFAULT '0',
`no_charge_reasons` varchar(255) DEFAULT NULL,
`no_charge_users` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_aos_quotes_type` (`type`),
KEY `idx_aos_quotes_rt_jobs_id` (`rt_jobs_id`),
KEY `idx_aos_quotes_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `rt_bids_aos_quotes_alternate` (
`id` varchar(36) NOT NULL,
`date_modified` datetime DEFAULT NULL,
`deleted` tinyint(1) DEFAULT '0',
`rt_bids_aos_quotes_altrt_bids_ida` varchar(36) DEFAULT NULL,
`rt_bids_aos_quotes_altaos_quotes_idb` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_rt_bids_aos_quotes_altrt_bids_ida_deleted` (`deleted`,`rt_bids_aos_quotes_altrt_bids_ida`),
KEY `rt_bids_aos_quotes_alt` (`rt_bids_aos_quotes_altrt_bids_ida`,`rt_bids_aos_quotes_altaos_quotes_idb`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
请告知我如何改进它。