MySQL Filesort/Order By Slow

Question

I've been struggling with a query for the past few weeks and it now just appears as gobbly-gook to me when I try to optimize. The query runs - at about 2.3 seconds, but I know there has to be a more efficient way of executing it. If I run it without the ORDER BY, 0.04 seconds. I can see from the EXPLAIN that there is a filesort and tmp table - but for the life of me, I cannot develop a query that doesn't use it. Please educate me!

Below is the query, structure and EXPLAIN statement.

RECORD COUNT:

+---------------------------------------------+------------+
| table_name                                  | TABLE_ROWS |
+---------------------------------------------+------------+
| cat_brands                                  |         24 |
| cat_images                                  |      31920 |
| cat_products                                |      46059 |
| cat_products_images                         |      32272 |
| cat_products_prices                         |      77000 |
| cat_products_ranges                         |    7226120 |
| cat_promotion                               |          2 |
| cat_range                                   |          5 |
| companies                                   |        237 |
| companies_suppliers_pricing_tiers           |        143 |
| companies_suppliers_pricing_tiers_companies |       8848 |
+---------------------------------------------+------------+

QUERY:

SELECT cp.id, cp.natbuild_title, cp.gtin, cpp.invoice_price_ex_gst, cppp.is_promotion, cpro.is_bbb, cpro.promotion_end_date, cppp.invoice_price_ex_gst AS promotion_invoice_price_ex_gst, cpro.title AS promotion_title, cpro.promotion_end_date, cppp.promotion_bonus_text, cppp.promotion_free_text, CONCAT(ci1.filepath, ci1.filename) AS product_stamp, cp.purchase_unit_of_measure, cp.supplier_code, s.company_name, s.id AS supplier_company_id, ci.filepath, ci.filename, cr.title, cr.abbr
FROM companies_suppliers_pricing_tiers_companies csptc
LEFT JOIN companies_suppliers_pricing_tiers cspt ON cspt.id = csptc.tier_id
LEFT JOIN cat_products_prices cpp ON cpp.tier_id = csptc.tier_id
LEFT JOIN cat_products cp ON cp.id = cpp.product_id
LEFT JOIN cat_products_images cpi ON (cpi.product_id = cp.id AND cpi.is_main_image = 1)
LEFT JOIN cat_images ci ON ci.id = cpi.image_id
LEFT JOIN cat_products_ranges cpr ON cpr.product_id = cp.id AND cpr.branch_id = 3
LEFT JOIN cat_range cr ON cr.id = cpr.range_id
LEFT JOIN companies s ON s.id = cp.company_supplier_id
LEFT JOIN cat_brands cb ON cb.id = cp.brand_id
LEFT JOIN cat_products_prices cppp ON (cppp.parent_id = cpp.id AND cppp.is_promotion = 1)
LEFT JOIN cat_promotion cpro ON (cpro.id = cppp.promotion_id AND promotion_start_date <= CURDATE() AND promotion_end_date >= CURDATE())
LEFT JOIN cat_images ci1 ON ci1.id = cpro.product_stamp_image_id
WHERE csptc.branch_id = 3 AND cpp.is_promotion IS NULL AND cp.active = 1 AND (cpp.effective_start_date <= CURDATE() OR cpp.effective_start_date IS NULL)
ORDER BY cr.sort_order ASC, natbuild_title ASC
LIMIT 0, 25;

EXPLAIN:

+----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-----------------------------------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                                                                         | key                                                   | key_len | ref                                           | rows | Extra                                              |
+----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-----------------------------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | csptc | ref    | companies_suppliers_pricing_tiers_companies_branch_id,companies_suppliers_pricing_tiers_companies_tier_id                             | companies_suppliers_pricing_tiers_companies_branch_id | 4       | const                                         |   58 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | cspt  | eq_ref | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | natbuild_tradenet.csptc.tier_id               |    1 | Using index                                        |
|  1 | SIMPLE      | cpp   | ref    | cat_products_prices_tier_id,cat_products_prices_product_id,cat_products_prices_effective_start_date,cat_products_prices_bbb_line_item | cat_products_prices_tier_id                           | 4       | natbuild_tradenet.csptc.tier_id               |  310 | Using where                                        |
|  1 | SIMPLE      | cp    | eq_ref | PRIMARY,cat_products_active                                                                                                           | PRIMARY                                               | 4       | natbuild_tradenet.cpp.product_id              |    1 | Using where                                        |
|  1 | SIMPLE      | cpi   | ref    | cat_products_images_is_main_image,cat_products_images_product_id_image_id                                                             | cat_products_images_product_id_image_id               | 4       | natbuild_tradenet.cpp.product_id              |    1 | Using where                                        |
|  1 | SIMPLE      | ci    | eq_ref | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | natbuild_tradenet.cpi.image_id                |    1 | NULL                                               |
|  1 | SIMPLE      | cpr   | ref    | cat_products_ranges_product_id,cat_products_ranges_branch_id,cat_products_ranges_branch_id_product_id                                 | cat_products_ranges_branch_id_product_id              | 8       | const,natbuild_tradenet.cpp.product_id        |    1 | NULL                                               |
|  1 | SIMPLE      | cr    | ALL    | PRIMARY                                                                                                                               | NULL                                                  | NULL    | NULL                                          |    5 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | s     | eq_ref | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | natbuild_tradenet.cp.company_supplier_id      |    1 | NULL                                               |
|  1 | SIMPLE      | cb    | eq_ref | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | natbuild_tradenet.cp.brand_id                 |    1 | Using index                                        |
|  1 | SIMPLE      | cppp  | ref    | cat_products_prices_bbb_line_item                                                                                                     | cat_products_prices_bbb_line_item                     | 2       | const                                         |    4 | Using where                                        |
|  1 | SIMPLE      | cpro  | eq_ref | PRIMARY,cat_promotions_promotion_start_date,cat_promotions_promotion_end_date                                                         | PRIMARY                                               | 4       | natbuild_tradenet.cppp.promotion_id           |    1 | Using where                                        |
|  1 | SIMPLE      | ci1   | eq_ref | PRIMARY                                                                                                                               | PRIMARY                                               | 4       | natbuild_tradenet.cpro.product_stamp_image_id |    1 | NULL                                               |
+----+-------------+-------+--------+---------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------+---------+-----------------------------------------------+------+----------------------------------------------------+

STRUCTURE:

CREATE TABLE `cat_brands` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_brands_company_name` (`company_name`),
  KEY `cat_brands_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_category_id` int(11) DEFAULT NULL,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `image_id` int(11) DEFAULT NULL,
  `parent_url_string` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `url_string` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `markup_amount` int(11) DEFAULT NULL,
  `is_fineline` int(11) DEFAULT NULL,
  `old_id` int(11) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_category_parent_category_id` (`parent_category_id`),
  KEY `cat_category_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=2831 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_ts` datetime NOT NULL,
  `filepath` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `is_high_resolution` tinyint(1) DEFAULT NULL,
  `image_type` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `filesize` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_images_filename` (`filename`),
  KEY `cat_images_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=64029 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_ts` datetime NOT NULL,
  `modified_ts` datetime NOT NULL,
  `parent_product_id` int(11) DEFAULT NULL,
  `company_supplier_id` int(11) NOT NULL,
  `company_owner_id` int(11) DEFAULT NULL COMMENT 'ID of the owner of the product (defaults to Natbuild)',
  `category_id` int(11) DEFAULT NULL,
  `gtin` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `parent_gtin` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_code` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `natbuild_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `brand_id` int(11) DEFAULT NULL,
  `short_description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `long_description` text COLLATE utf8_unicode_ci,
  `specifications` text COLLATE utf8_unicode_ci,
  `bio` text COLLATE utf8_unicode_ci,
  `features_benefits` text COLLATE utf8_unicode_ci,
  `unit` int(11) DEFAULT NULL,
  `qty_in_unit` decimal(12,4) DEFAULT NULL COMMENT 'Quantity of pieces in unit',
  `purchase_unit_of_measure` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'How members purchase the product',
  `conversion_factor` decimal(12,4) DEFAULT NULL COMMENT 'How many units the members can sell individually',
  `sell_unit_of_measure` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'How members sell the product',
  `unit_height` int(11) DEFAULT NULL COMMENT 'In mm',
  `unit_width` int(11) DEFAULT NULL COMMENT 'In mm',
  `unit_depth` int(11) DEFAULT NULL COMMENT 'In mm',
  `piece_height` int(11) DEFAULT NULL COMMENT 'In mm',
  `piece_width` int(11) DEFAULT NULL COMMENT 'In mm',
  `piece_depth` int(11) DEFAULT NULL COMMENT 'In mm',
  `total_length` int(11) DEFAULT NULL COMMENT 'In M',
  `gross_weight_kg` decimal(12,4) DEFAULT NULL COMMENT 'In kilograms',
  `gross_volume_l` decimal(12,4) DEFAULT NULL COMMENT 'In litres',
  `cubic_volume` decimal(12,4) DEFAULT NULL COMMENT 'In m3',
  `next_lower_level_gtin` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hhg_un_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hhg_class_number` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hhg_subsidiary_risk` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hhg_packaging_group` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hhg_hazchem` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_competitive` int(11) DEFAULT NULL COMMENT 'Is this a competitive price product?',
  `is_featured` tinyint(1) DEFAULT NULL COMMENT 'Is this a featured product?',
  `is_large_featured` tinyint(1) DEFAULT NULL COMMENT 'Is this a large featured product?',
  `old_id` int(11) DEFAULT NULL,
  `old_range_id` int(11) DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_products_category_id` (`category_id`),
  KEY `cat_products_gtin` (`gtin`),
  KEY `cat_products_supplier_code` (`supplier_code`),
  KEY `cat_products_company_supplier_id` (`company_supplier_id`),
  KEY `cat_products_is_featured` (`is_featured`),
  KEY `cat_products_is_large_featured` (`is_large_featured`),
  KEY `cat_products_natbuild_title` (`natbuild_title`),
  KEY `cat_products_brand_id` (`brand_id`),
  KEY `cat_products_active` (`active`),
  KEY `cat_products_company_owner_id` (`company_owner_id`),
  KEY `cat_products_is_competitive` (`is_competitive`),
  FULLTEXT KEY `cat_products_natbuild_title_ft` (`natbuild_title`),
  CONSTRAINT `fk1_cat_category_id` FOREIGN KEY (`category_id`) REFERENCES `cat_category` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk1_cat_products_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `cat_brands` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk1_cat_products_company_owner_id` FOREIGN KEY (`company_owner_id`) REFERENCES `companies` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk1_cat_products_company_supplier_id` FOREIGN KEY (`company_supplier_id`) REFERENCES `companies` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=95293 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_products_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_ts` datetime NOT NULL,
  `product_id` int(11) NOT NULL DEFAULT '0',
  `image_id` int(11) NOT NULL DEFAULT '0',
  `valid_from_date` date DEFAULT NULL,
  `sort_order` tinyint(2) NOT NULL DEFAULT '1',
  `is_main_image` tinyint(1) DEFAULT NULL,
  `is_large_featured_image` tinyint(1) DEFAULT NULL,
  `is_small_featured_image` tinyint(1) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_products_images_image_id` (`image_id`),
  KEY `cat_products_images_is_main_image` (`is_main_image`),
  KEY `cat_products_images_product_id_image_id` (`product_id`,`image_id`),
  KEY `cat_products_images_active` (`active`),
  KEY `cat_products_images_is_large_featured_image` (`is_large_featured_image`),
  KEY `cat_products_images_is_small_featured_image` (`is_small_featured_image`),
  CONSTRAINT `fk1_cat_products_images_image_id` FOREIGN KEY (`image_id`) REFERENCES `cat_images` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk1_cat_products_images_product_id` FOREIGN KEY (`product_id`) REFERENCES `cat_products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=64021 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_products_prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `entry_ts` datetime DEFAULT NULL,
  `modified_ts` datetime DEFAULT NULL,
  `product_id` int(11) NOT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `tier_id` int(11) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `delivery_type` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Whether this price if for pickup in store or delivered to site',
  `volume_break` tinyint(1) DEFAULT NULL COMMENT 'Whether this line item is simply a volume break (no repeated information, pricing only)',
  `list_price_ex_gst` decimal(12,4) DEFAULT NULL COMMENT 'Price ex. GST before any discounts',
  `gst_rate` decimal(5,2) DEFAULT NULL COMMENT 'GST rate for invoice price',
  `rrp_inc_gst` decimal(12,4) DEFAULT NULL COMMENT 'RRP inc. GST',
  `vol_discount_min_qty` decimal(12,4) DEFAULT NULL COMMENT 'Volume discount minimum quantity',
  `vol_discount_min_qty_uom` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Volume discount minimum quantity unit of measure',
  `vol_discount_percent` decimal(4,2) DEFAULT NULL COMMENT 'Volume discount percentage',
  `vol_discount_net_price` decimal(12,4) DEFAULT NULL COMMENT 'Volume discount net price',
  `trade_discount_amount` decimal(12,4) DEFAULT NULL COMMENT 'Trade discount flat amount for this product',
  `trade_discount_percent` decimal(4,2) DEFAULT NULL COMMENT 'Trade discount percentage for this product',
  `invoice_price_ex_gst` decimal(12,4) DEFAULT NULL COMMENT 'Final invoice price ex. GST (list minus discount)',
  `min_order_qty` decimal(12,4) DEFAULT NULL,
  `min_order_qty_uom` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `order_qty_multiple` decimal(12,4) DEFAULT NULL,
  `order_qty_multiple_uom` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `effective_start_date` date DEFAULT NULL COMMENT 'The date this price is valid from',
  `effective_end_date` date DEFAULT NULL COMMENT 'The date this price is valid to',
  `is_promotion` tinyint(1) DEFAULT NULL COMMENT 'Whether this line item is a promotional item',
  `promotion_id` int(11) DEFAULT NULL COMMENT 'The ID of the promotion from cat_promotion table',
  `promotion_bonus_text` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Bonus text for this product',
  `promotion_free_text` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Free text field (if required)',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_products_prices_tier_id` (`tier_id`),
  KEY `cat_products_prices_branch_id` (`branch_id`),
  KEY `cat_products_prices_product_id` (`product_id`),
  KEY `cat_products_prices_effective_start_date` (`effective_start_date`),
  KEY `cat_products_prices_effective_end_date` (`effective_end_date`),
  KEY `cat_products_prices_bbb_line_item` (`is_promotion`),
  KEY `cat_products_prices_active` (`active`),
  KEY `cat_products_promotion_id` (`promotion_id`),
  CONSTRAINT `kf1_cat_products_prices_branch_id` FOREIGN KEY (`branch_id`) REFERENCES `companies_branches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `kf1_cat_products_prices_product_id` FOREIGN KEY (`product_id`) REFERENCES `cat_products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `kf1_cat_products_prices_tier_id` FOREIGN KEY (`tier_id`) REFERENCES `companies_suppliers_pricing_tiers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `kf1_cat_products_promotion_id` FOREIGN KEY (`promotion_id`) REFERENCES `cat_promotion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=153107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_products_ranges` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL DEFAULT '0',
  `branch_id` int(11) NOT NULL DEFAULT '0',
  `range_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `cat_products_ranges_product_id` (`product_id`),
  KEY `cat_products_ranges_branch_id` (`branch_id`),
  KEY `cat_products_ranges_range_id` (`range_id`),
  KEY `cat_products_ranges_branch_id_product_id` (`branch_id`,`product_id`),
  CONSTRAINT `fk1_cat_products_ranges_product_id` FOREIGN KEY (`product_id`) REFERENCES `cat_products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cat_products_ranges_branch_id` FOREIGN KEY (`branch_id`) REFERENCES `companies_branches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cat_products_ranges_range_id` FOREIGN KEY (`range_id`) REFERENCES `cat_range` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14841797 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_promotion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `major_prize` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `est_delivery_date` date NOT NULL,
  `catalogue_download_id` int(11) NOT NULL,
  `supplier_company_id` int(11) NOT NULL,
  `promotion_url` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `banner_image_id` int(11) DEFAULT NULL,
  `teaser_image_id` int(11) DEFAULT NULL,
  `sidebar_image_id` int(11) DEFAULT NULL,
  `product_stamp_image_id` int(11) DEFAULT NULL,
  `order_start_date` date NOT NULL,
  `order_end_date` date NOT NULL,
  `promotion_start_date` date NOT NULL,
  `promotion_end_date` date NOT NULL,
  `is_bbb` tinyint(1) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_promotions_promotion_start_date` (`promotion_start_date`),
  KEY `cat_promotions_promotion_end_date` (`promotion_end_date`),
  KEY `cat_promotions_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `cat_range` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `abbr` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sort_order` smallint(2) DEFAULT NULL,
  `active` tinyint(4) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `cat_range_abbr` (`abbr`),
  KEY `cat_range_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `company_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `full_company_name` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `address1` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address3` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `suburb` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `state` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `postcode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `country` varchar(2) COLLATE utf8_unicode_ci DEFAULT 'AU',
  `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `fax` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_address` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `website` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `supplier_category_manager_id` int(11) DEFAULT NULL,
  `supplier_support_level_id` int(11) DEFAULT NULL,
  `supplier_gln` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `logo_path` varchar(256) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login` datetime DEFAULT NULL,
  `old_id` int(11) DEFAULT NULL,
  `is_ecat_supplier` tinyint(1) DEFAULT '0',
  `nims_id` int(11) DEFAULT NULL,
  `old_zoneid` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `updated_ts` timestamp NULL DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `companies_company_type_nims_id` (`company_type`,`nims_id`),
  KEY `companies_client_name` (`company_name`),
  KEY `companies_state` (`state`),
  KEY `companies_company_type` (`company_type`),
  KEY `companies_supplier_support_level_id` (`supplier_support_level_id`),
  KEY `fk_companies_supplier_category_manager_id` (`supplier_category_manager_id`),
  CONSTRAINT `fk_companies_supplier_category_manager_id` FOREIGN KEY (`supplier_category_manager_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_companies_supplier_support_level_id` FOREIGN KEY (`supplier_support_level_id`) REFERENCES `companies_suppliers_support_levels` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=901 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `companies_suppliers_pricing_tiers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL COMMENT 'ID of the supplier',
  `tier_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Name of the pricing tier',
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Description of the tier (if required)',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `companies_suppliers_pricing_tiers_tier_name` (`tier_name`),
  KEY `companies_suppliers_pricing_tiers_company_id` (`company_id`),
  KEY `companies_suppliers_pricing_tiers_active` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=287 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `companies_suppliers_pricing_tiers_companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `branch_id` int(11) NOT NULL DEFAULT '0',
  `tier_id` int(11) NOT NULL DEFAULT '0',
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `companies_suppliers_pricing_tiers_companies_branch_id` (`branch_id`),
  KEY `companies_suppliers_pricing_tiers_companies_tier_id` (`tier_id`),
  KEY `companies_suppliers_pricing_tiers_companies_active` (`active`),
  CONSTRAINT `fk1_companies_suppliers_pricing_tiers_companies_branch_id` FOREIGN KEY (`branch_id`) REFERENCES `companies_branches` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk1_companies_suppliers_pricing_tiers_companies_tier_id` FOREIGN KEY (`tier_id`) REFERENCES `companies_suppliers_pricing_tiers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17795 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

Any help would be greatly appreciated.

Cheers,

Craig.

@oNare, thanks, have added index with no change in execution time (even after ANALYZE operation). — Craig McCormack, Jul 6, 2015 at 3:04
@CraigMcCormack "ORDER BY cr.sort_order ASC, natbuild_title ASC" means you are sorting on two columns from two different tables? that HAS to do filesort always, no way to index it "across" tables, so mysql has to get all rows, sort them and then pick 25 first ones (without order by it is 25 "random" fast ones. And you are sorting on columns from felt-joined tables too - those could be null, so ordered a bit randomly - but you are using WHERE on some of other left-joined column (cp.active = 1) so that messes with it too. Make your mind about what do you need and try to clean it up a bit. — jkavalik, Jul 6, 2015 at 19:50
@jkavalik, thanks for your comments. I see your point regarding the sort columns spanning 2 tables - yes, that is correct. This is the fastest iteration of the query I've been able to produce with the absolute minimal amount of information. The variations to the query (and what the user can filter by) are vast which is why there are so many left joins. Essentially each product can have: a) a price based on each branch location, b) a range based on each branch location and c) an over-riding promotional price. — Craig McCormack, Jul 7, 2015 at 2:39

Rick James · Accepted Answer · 2015-07-06 21:46:28Z

1

Learn about composite indexes; use them where appropriate. For example:

ON cpr.product_id = cp.id AND cpr.branch_id = 3

begs for either of these:

INDEX(product_id, branch_id)
INDEX(branch_id, product_id)

Indexing flags (such as active) rarely useful.

Don't use LEFT unless the "right" table has optional data. What do you expect the ORDER BY to do if you never get to cr.sort_order (etc)?

answered Jul 6, 2015 at 21:46

Rick James

74.8k5 gold badges44 silver badges105 bronze badges

thanks for your comments. In relation to the composite index, are you suggesting that I force the index (as it already exists on the table). Is this just a matter of adding INDEX(xxxx,xxxx) to force its use? In relation to LEFT, are you suggesting using INNER instead? You are correct in that some tables that I've LEFT joined have optional data, and some are always populated.
– Craig McCormack
Jul 7, 2015 at 2:42
1

Sorry, I failed to spot that you have the composite index. Do not FORCE INDEX; that tends to cause trouble in the long run. Changing LEFT to INNER gives the optimizer more choices; that might lead to a better order of hitting the tables. Keep LEFT whenever you want NULLs for optional data.
– Rick James
Jul 7, 2015 at 2:49
How much RAM do you have? What is the value of innodb_buffer_pool_size?
– Rick James
Jul 7, 2015 at 2:50
Thanks Rick. I have approx. 700mb of data across the entire schema, and have a 4-core, 7GB Azure instance with Ubuntu 14.04LTS. Nginx server and MySQL running in Master-Master mode (currently disabled until I can improve this query). innodb_buffer_pool_size = 4GB. I've been working on the MySQL configuration over the past 6 months to tweak, so it's fairly customised. I'll give the INNER alterations a try (saying that, I can't help thinking that I've already been down this path).
– Craig McCormack
Jul 7, 2015 at 23:02

Add a comment |

Stack Exchange Network

MySQL Filesort/Order By Slow

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
performance
optimization
order-by
temporary-tables
query-performance
or ask your own question.

Hot Network Questions

MySQL Filesort/Order By Slow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqlperformanceoptimizationorder-bytemporary-tablesquery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
performance
optimization
order-by
temporary-tables
query-performance
or ask your own question.