Need to optimize mysql query of a PHP framework

Question

I have a query which is generated via PHP framework core function. I don't have control to change the query. Therefore i will need to perform optimization on server side i.e. mysql to execute this query in efficient time. I have applied some indices but still it is taking around 4-5 seconds and ideally it should take 1-1.5 seconds. Following is the query:

(
  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'
)

Following is the Explain query result: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    <union1,2>  ALL NULL    NULL    NULL    NULL    NULL    Using temporary

Show create table results :

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;

Please advise how can i improve it.

Questions about query performance always require SHOW CREATE TABLE statements for all relevant tables as well as the EXPLAIN — Strawberry, Sep 21 '17 at 05:53
@Strawberry please see updated question body. I put all the details. — RokiE, Sep 21 '17 at 06:14

Bill Karwin · Accepted Answer · 2017-09-21T06:41:00.633

Your default charset for aos_quotes is latin1, but the default charset for the other tables is utf8. When you JOIN based on comparing two strings with different collations, they can't use the index, so they are forced to do a table-scan. I have no doubt that's what's slowing down your query.

When I use your tables as is, I get this EXPLAIN for the so tables:

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: so
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

When I convert your aos_quotes table to utf8, I get this EXPLAIN for the so tables:

*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: so
   partitions: NULL
         type: const
possible_keys: PRIMARY,idx_aos_quotes_id
          key: PRIMARY
      key_len: 108
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

The "type: PRIMARY" is much better than "type: ALL".

So you need to convert your aos_quotes table to utf8.

See How do I change a MySQL table to UTF-8?

Thank you Bill.It seems that performance got much improved. Let me give some more tries and i will select this an answer if everything worked smoothly. Moreover there so is no other suggestion to improve further? — RokiE, Sep 21 '17 at 06:44

score 1 · Answer 2 · answered Sep 21 '17 at 22:46

Other possible improvements:

A PRIMARY KEY is a UNIQUE key is a KEY. So KEY idx_aos_quotes_id (id) is redundant and could (should) be dropped.
I see VARCHAR(36); that usually smacks of random UUIDs. But your query shows something else. What is the situation. (The randomness of standard UUIDs is a performance problem in large tables.)
I see lots of TEXT columns and SELECT .. so.* fetching all of them. If you don't need them all, don't fetch them all. Each one is potentially an extra disk hit.
UNION defaults to UNION DISTINCT, which involves a de-dup pass. If you don't need that, then UNION ALL would be faster.
(Not a speed issue, just a readability issue.) Can you remove the clutter of the "rt_bids_aos_quotes_" prefix?
If rt_bids_aos_quotes_alternate and rt_bids_aos_quotes_rel are many:many mappings, see my blog for several performance tips.
decimal(26,6) takes 12 bytes. Do you need that much precision and range? Or would some other datatype do? Smaller -> more cacheable -> less I/O -> faster. (Rule of Thumb: DECIMAL(m,n) occupies about m/2 bytes. INT and FLOAT occupy exactly 4 bytes. TINYINT: 1 byte.)
In addition to fixing the CHARSET for id, fix the inconsistency between CHAR(36) and VARCHAR(36), especially if your ids are only 15 characters. (Again, space may lead to speed.)
I see UNIQUE(deleted, ida) -- Does this mean that there can be 1 or 2 rows with a given ida? If there can be only one (either deleted or not), then make (ida) the PK.

Need to optimize mysql query of a PHP framework

2 Answers2