0

Any ideas how to improve and what the actual culprit is?

records in product are only 24000

takes 0.85s to run

Mysql 5.7

select `product`.* from `product` 
 inner join `manufacturer` on `product`.`manufacturer_id` = `manufacturer`.`id` 
 left join `manufacturer_bundle` on `manufacturer_bundle`.`id` =  `manufacturer`.`manufacturer_bundle_id` 
 left join `color` on `product`.`color_id` = `color`.`id` 
 left join `product_code_upc` on `product`.`product_code` = `product_code_upc`.`product_code` 
 inner join `supplier` on `supplier`.`id` = `product`.`supplier_id` 
 left join `color_code` on `product`.`color_code_id` = `color_code`.`id` and `manufacturer`.`id` = `color_code`.`manufacturer_id` 
where `product`.`available` > 0 and `product`.`is_bought_copy` is null 
  and `product`.`deleted_at` is null 
order by case
 when `product`.`product_code` LIKE "QR-0012-ft" then  1
 when `product`.`product_code` LIKE "QR-0012-gt" then 2
 when `product`.`product_code` LIKE "QR-0012-at" then  3
 when `product`.`product_code` LIKE "QR-0012-xt" then 4
 when `product`.`product_code` LIKE "QR-0012-ut" then  5
 when `product`.`product_code` LIKE "QA-0012-ft" then 6
 when `product`.`product_code` LIKE "QQ-0012-ft" then  7
 else 11 end, 
 `supplier`.`state` desc, 
 `product`.`product_code` desc, 
 `manufacturer`.`name` desc 
limit 15 offset 0

The explain:

+----+-------------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+--------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------+
| id | select_type | table               | partitions | type        | possible_keys                                                                                                                         | key                                                   | key_len | ref                                              | rows | filtered | Extra                                                                                                                |
+----+-------------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+--------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | product             | NULL       | index_merge | product_is_bought_copy_index,product_manufacturer_id_index,product_available_index,product_supplier_id_index,product_deleted_at_index | product_is_bought_copy_index,product_deleted_at_index | 2,5     | NULL                                             | 6431 |    50.00 | Using intersect(product_is_bought_copy_index,product_deleted_at_index); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | manufacturer        | NULL       | eq_ref      | PRIMARY                                                                                                                               | PRIMARY                                               | 8       | plum_laravel.product.manufacturer_id             |    1 |   100.00 | Using where                                                                                                          |
|  1 | SIMPLE      | manufacturer_bundle | NULL       | eq_ref      | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | plum_laravel.manufacturer.manufacturer_bundle_id |    1 |   100.00 | Using where; Using index                                                                                             |
|  1 | SIMPLE      | color               | NULL       | eq_ref      | PRIMARY                                                                                                                               | PRIMARY                                               | 8       | plum_laravel.product.color_id                    |    1 |   100.00 | Using where; Using index                                                                                             |
|  1 | SIMPLE      | product_code_upc    | NULL       | ref         | product_code_upc_product_code_index                                                                                                   | product_code_upc_product_code_index                   | 767     | plum_laravel.product.product_code                |    1 |   100.00 | Using index                                                                                                          |
|  1 | SIMPLE      | supplier            | NULL       | eq_ref      | PRIMARY                                                                                                                               | PRIMARY                                               | 8       | plum_laravel.product.supplier_id                 |    1 |   100.00 | Using where                                                                                                          |
|  1 | SIMPLE      | color_code          | NULL       | eq_ref      | PRIMARY,color_code_manufacturer_id_index                                                                                              | PRIMARY                                               | 4       | plum_laravel.product.color_code_id               |    1 |   100.00 | Using where                                                                                                          |
+----+-------------+---------------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+--------------------------------------------------+------+----------+----------------------------------------------------------------------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

As requested the SHOW CREATE TABLEs:

CREATE TABLE `product` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `manufacturer_id` int(10) unsigned DEFAULT NULL,
  `color_id` int(10) unsigned DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `upc_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` double DEFAULT NULL,
  `available` int(11) DEFAULT NULL,
  `supplier_id` int(10) unsigned DEFAULT NULL,
  `supplier_product_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_condition_id` int(11) DEFAULT NULL,
  `note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image_url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `weight` double DEFAULT NULL,
  `is_order_history` tinyint(1) DEFAULT NULL,
  `color_code_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `is_bought_copy` tinyint(1) DEFAULT NULL,
  `bought_product_id` bigint(20) DEFAULT NULL,
  `description_uuid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_product_code_index` (`product_code`),
  KEY `product_color_code_id_index` (`color_code_id`),
  KEY `product_created_at_index` (`created_at`),
  KEY `product_updated_at_index` (`updated_at`),
  KEY `product_is_bought_copy_index` (`is_bought_copy`),
  KEY `product_bought_product_id_index` (`bought_product_id`),
  KEY `product_description_uuid_index` (`description_uuid`),
  KEY `product_price_index` (`price`),
  KEY `product_upc_code_index` (`upc_code`),
  KEY `product_description_index` (`description`),
  KEY `product_color_id_index` (`color_id`),
  KEY `product_manufacturer_id_index` (`manufacturer_id`),
  KEY `product_state_index` (`state`),
  KEY `product_product_condition_id_index` (`product_condition_id`),
  KEY `product_available_index` (`available`),
  KEY `product_supplier_id_index` (`supplier_id`),
  KEY `product_supplier_product_code_index` (`supplier_product_code`),
  KEY `product_deleted_at_index` (`deleted_at`)
) ENGINE=InnoDB AUTO_INCREMENT=681005 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 


CREATE TABLE `manufacturer` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `manufacturer_bundle_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `manufacturer_name_index` (`name`),
  KEY `manufacturer_manufacturer_bundle_id_index` (`manufacturer_bundle_id`),
  KEY `manufacturer_created_at_index` (`created_at`),
  KEY `manufacturer_updated_at_index` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=306 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |



CREATE TABLE `manufacturer_bundle` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `manufacturer_bundle_created_at_index` (`created_at`),
  KEY `manufacturer_bundle_updated_at_index` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=226 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |



CREATE TABLE `color` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `color_bundle_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `color_name_index` (`name`),
  KEY `color_color_bundle_id_index` (`color_bundle_id`),
  KEY `color_created_at_index` (`created_at`),
  KEY `color_updated_at_index` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=3256 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |



CREATE TABLE `product_code_upc` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer_bundle_id` int(11) NOT NULL,
  `product_code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `product_code_nospecial` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `upc_code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_code_upc_manufacturer_bundle_id_index` (`manufacturer_bundle_id`),
  KEY `product_code_upc_product_code_index` (`product_code`),
  KEY `product_code_upc_product_code_nospecial_index` (`product_code_nospecial`),
  KEY `product_code_upc_upc_code_index` (`upc_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci



CREATE TABLE `supplier` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `plum_account_nr` bigint(20) unsigned NOT NULL,
  `company_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lastname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `position` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `wantsheet_supplier_id` bigint(20) DEFAULT NULL,
  `accountant_supplier_id` bigint(20) DEFAULT NULL,
  `type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `inventory_listing` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `supplier_plum_account_nr_index` (`plum_account_nr`),
  KEY `supplier_company_name_index` (`company_name`),
  KEY `supplier_firstname_index` (`firstname`),
  KEY `supplier_lastname_index` (`lastname`),
  KEY `supplier_position_index` (`position`),
  KEY `supplier_address1_index` (`address1`),
  KEY `supplier_address2_index` (`address2`),
  KEY `supplier_city_index` (`city`),
  KEY `supplier_zip_index` (`zip`),
  KEY `supplier_state_index` (`state`),
  KEY `supplier_wantsheet_supplier_id_index` (`wantsheet_supplier_id`),
  KEY `supplier_accountant_supplier_id_index` (`accountant_supplier_id`),
  KEY `supplier_type_index` (`type`),
  KEY `supplier_inventory_listing_index` (`inventory_listing`),
  KEY `supplier_created_at_index` (`created_at`),
  KEY `supplier_updated_at_index` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=595 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 



CREATE TABLE `color_code` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `manufacturer_id` int(11) NOT NULL,
  `color_bundle_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `color_code_code_index` (`code`),
  KEY `color_code_manufacturer_id_index` (`manufacturer_id`),
  KEY `color_code_color_bundle_id_index` (`color_bundle_id`),
  KEY `color_code_created_at_index` (`created_at`),
  KEY `color_code_updated_at_index` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


6
  • Give us the output of SHOW CREATE TABLE xyz\G for all the tables in your query. Please do not upload images for the reasons outlined in this link - use text instead... – Vérace Apr 9 at 19:11
  • How much time do you think the query should take, and why? – mustaccio Apr 9 at 19:31
  • @mustaccio well, I was hoping it would take A LOT less time especially since it's only a few rows. Why? well I'm the one asking. Not sure why you are asking me? seems a bit passive aggressive to me. You ok my dude? – Toskan Apr 9 at 20:07
  • @Vérace I added explain in text and show create as well, hope it is helpful somehow – Toskan Apr 9 at 20:15
  • 1
    For one, you probably shouldn't be joining the tables that are not used in obtaining the result; you really need only three. – mustaccio Apr 9 at 20:42
2

Using intersect almost always means that you should add a composite index.

where `product`.`available` > 0
  and `product`.`is_bought_copy` is null 
  and `product`.`deleted_at` is null 

needs

INDEX(is_bought_copy, deleted_at,   -- in either order, first since "="
      available)                    -- last, since "range"

That index will help some, but probably not a lot.

The ORDER BY cannot be optimized, nor helped by an index. So must find all the possible rows, sort them, then finally deliver the first 15.

Shrinking the (255) to realistic limits might help some. (It might avoid an on-disk temp table.) Also, if you don't need all the columns of product, specify only the columns needed.

Also

  • Get rid of the JOINs that don't lead to anything, such as color and color_code.
  • Some columns seem redundant -- color & color_code; description and description_uuid.
  • Get rid of created_at and/or updated_at unless you actually use them.
  • Get rid of any indexes on flags; they are unlikely to be useful.
3
  • uff, love it... you are right! didn't even notice the joins that lead to nothing... I see the order by is the culprit though. That is why I somehow would love materialized views... is there a different way to side step the order by problem? like that the select naturally returns everything in the right order off the bat? I can as well cache on the application layer I guess – Toskan Apr 11 at 3:59
  • @Toskan - It should be possible to avoid that CASE in MySQL 8.0 (cf "generated column"), but there are still 3 tables involved in the ORDER BY. That prevents using any index to avoid having a big sort before getting to the LIMIT. – Rick James Apr 11 at 4:39
  • understood. Isn't there a way to create the primary key in a way that it spits out the results always in the order that I want (or something like that)? problem is, it's hacky eh. I will probably just stick to caching... – Toskan Apr 11 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.