Mysql – How to optimize this MySQL query

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 ;