php mysql 查询内存溢出_mysql - 需要优化PHP框架的mysql查询 - 堆栈内存溢出

我有一个通过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;

请告知我如何改进它。