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.
Best Answer
Learn about composite indexes; use them where appropriate. For example:
begs for either of these:
Indexing flags (such as
active) rarely useful.Don't use
LEFTunless the "right" table has optional data. What do you expect theORDER BYto do if you never get tocr.sort_order(etc)?