2

Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query

SELECT vendor_products.*, 
       (SELECT Count(*) 
        FROM   products 
        WHERE  gtin = vendor_products.gtin 
                OR gtin = Concat(0, vendor_products.gtin) 
                OR gtin = Substr(vendor_products.gtin, 2)) AS product_count 
FROM   vendor_products 
WHERE  vendor_products.vendor_id != 26 
       AND vendor_products.cost > 0 
       AND vendor_products.short_description != '' 
       AND created_at >= '2014-07-01 00:00:00' 
HAVING product_count = 0 
LIMIT  100

Can someone guide me and tell what I can improve?

CREATE TABLE IF NOT EXISTS `vendor_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL,
  `gtin` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sku` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mpn` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `weight` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
  `inventory` int(11) NOT NULL DEFAULT '0',
  `cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `price` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `nav` tinyint(4) NOT NULL DEFAULT '0',
  `lemil` tinyint(4) NOT NULL DEFAULT '0',
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `short_description` text COLLATE utf8_unicode_ci,
  `long_description` mediumtext COLLATE utf8_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `vendor_products_vendor_id_index` (`vendor_id`),
  KEY `vendor_products_gtin_index` (`gtin`),
  KEY `vendor_products_sku_index` (`sku`),
  KEY `deleted` (`deleted`),
  KEY `lemil` (`lemil`),
  KEY `nav` (`nav`),
  KEY `mpn` (`mpn`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=340210 ;

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `manufacturer_id` int(10) unsigned DEFAULT NULL,
  `gtin` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sku` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mpn` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `new_insert` tinyint(4) NOT NULL DEFAULT '0',
  `product_type` int(11) DEFAULT NULL,
  `vendor_id` int(11) DEFAULT NULL,
  `store_id` int(11) NOT NULL DEFAULT '0',
  `nav` tinyint(4) NOT NULL DEFAULT '0',
  `lemil` tinyint(4) NOT NULL DEFAULT '0',
  `package` tinyint(4) NOT NULL DEFAULT '0',
  `used` tinyint(4) NOT NULL DEFAULT '0',
  `free_shipping` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `short_description` mediumtext COLLATE utf8_unicode_ci,
  `full_description` longtext COLLATE utf8_unicode_ci,
  `force_msrp` tinyint(4) NOT NULL DEFAULT '0',
  `msrp` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `price` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `old_price` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `product_cost` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `special_price` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `special_price_start_date` datetime DEFAULT '0000-00-00 00:00:00',
  `special_price_end_date` datetime DEFAULT '0000-00-00 00:00:00',
  `outdoor_daily_price` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `map_high` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `map_low` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `stock_quantity` int(11) NOT NULL DEFAULT '0',
  `inbound_stock_quantity` int(11) NOT NULL DEFAULT '0',
  `in_stock_message` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `out_of_stock_message` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inbound_user` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `weight` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `length` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `width` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `height` decimal(18,4) NOT NULL DEFAULT '0.0000',
  `meta_keywords` text COLLATE utf8_unicode_ci,
  `meta_description` text COLLATE utf8_unicode_ci,
  `inventory_update` datetime DEFAULT NULL,
  `hide_from_guests` tinyint(4) NOT NULL DEFAULT '0',
  `published` tinyint(4) NOT NULL DEFAULT '1',
  `published_by` int(11) DEFAULT NULL,
  `publish_date` datetime DEFAULT NULL,
  `deleted` tinyint(4) NOT NULL DEFAULT '0',
  `deleted_on` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `products_gtin_index` (`gtin`),
  KEY `products_sku_index` (`sku`),
  KEY `products_new_insert_index` (`new_insert`),
  KEY `products_product_type_index` (`product_type`),
  KEY `products_vendor_id_index` (`vendor_id`),
  KEY `products_store_id_index` (`store_id`),
  KEY `products_nav_index` (`nav`),
  KEY `products_lemil_index` (`lemil`),
  KEY `products_package_index` (`package`),
  KEY `products_used_index` (`used`),
  KEY `products_free_shipping_index` (`free_shipping`),
  KEY `products_price_index` (`price`),
  KEY `products_hide_from_guests_index` (`hide_from_guests`),
  KEY `products_published_index` (`published`),
  KEY `products_deleted_index` (`deleted`),
  KEY `products_manufacturer_id_index` (`manufacturer_id`),
  KEY `products_published_by_index` (`published_by`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=117624 ;
Improve this question
4
  • Give us a SHOW CREATE TABLE vendor_products\G (and products) and even some sample data - either on SQLFiddle or as a download. That would help those who wish to help you.
    – Vérace
    Commented Jun 10, 2015 at 0:00
  • I have added table definitions, right now I am not able to put anything on SQLFiddle
    – SeeKnowledge
    Commented Jun 10, 2015 at 0:19
  • 1
    You should post the new query as an answer. It is equivalent to the first one.
    – ypercubeᵀᴹ
    Commented Jun 10, 2015 at 1:14
  • Thanks guys, I have moved the query I've proposed as an answer
    – SeeKnowledge
    Commented Jun 10, 2015 at 1:59

1 Answer 1

Reset to default
2

I have found out the solution by looking other similar questions, this query runs fast. Hopefully it will help someone that is having similar issue.

SELECT    vendor_products.*
FROM      vendor_products 
LEFT JOIN products a 
ON        a.gtin = vendor_products.gtin 
LEFT JOIN products b 
ON        b.gtin = Concat(0, vendor_products.gtin) 
LEFT JOIN products c 
ON        c.gtin = Substr(vendor_products.gtin, 2) 
WHERE     a.id IS NULL 
AND       b.id IS NULL 
AND       c.id IS NULL 
AND       vendor_products.vendor_id != 26 
AND       vendor_products.cost > 0 
AND       vendor_products.short_description != '' 
AND       vendor_products.created_at >= '2014-07-01 00:00:00' limit 100

If someone have even better solution please offer it here.

I did explain on answer query and this is the result enter image description here

The explain on original question query looks like this enter image description here

So there is 100.000+ more rows for search with the original approach no wonder why it was so slow.

Improve this answer
3
  • 2
    My guess - as to why this is faster - is that you replaced the OR with 3 independent joins and the indexes (on the gtin columns) can be used more effectively, especially for the first of the left join. If you provide the EXPLAIN outputs for the 2 queries, we may see.
    – ypercubeᵀᴹ
    Commented Jun 10, 2015 at 2:09
  • Yep you are right, there is 100.000+ rows to search with original query
    – SeeKnowledge
    Commented Jun 10, 2015 at 15:22
  • Yes, the key difference in the explain plan is the ref column. You see a NULL in the original query's plan. In the 2nd query, there is an index there and a func (the function you have applied to the indexed column). So, the vendor_products.gtin index can be used against the other gtin index.
    – ypercubeᵀᴹ
    Commented Jun 10, 2015 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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