ucy3000

SELECT 
  MAX(a.attribute_id) attribute_id, 
  MAX(ad.name) attribute_name, 
  MAX(pa.text) value, 
  COUNT(*) total 
FROM 
  psy_product p 
  LEFT JOIN psy_product_attribute pa ON (p.product_id = pa.product_id) 
  LEFT JOIN psy_attribute a ON (a.attribute_id = pa.attribute_id) 
  LEFT JOIN psy_attribute_description ad ON (ad.attribute_id = a.attribute_id) 
  LEFT JOIN psy_product_description pd ON (p.product_id = pd.product_id) 
  LEFT JOIN psy_product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE 
  p.status = '1' 
  AND p.date_available <= NOW() 
  AND p2s.store_id = '0' 
  AND pd.language_id = '1' 
  AND p.quantity > 0 
  AND pa.language_id = '1' 
  AND ad.language_id = '1' 
GROUP BY 
  lower(pa.text), 
  a.attribute_id 
HAVING 
  COUNT(*) > 0;

+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys           | key     | key_len | ref                                                 | rows  | filtered | Extra                                      |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
|  1 | SIMPLE      | pd    | NULL       | index  | PRIMARY                 | name    | 767     | NULL                                                | 38629 |    10.00 | Using where; Using index; Using temporary  |
|  1 | SIMPLE      | p     | NULL       | eq_ref | PRIMARY,status,quantity | PRIMARY | 4       | test_db.pd.product_id                               |     1 |     8.33 | Using where                                |
|  1 | SIMPLE      | p2s   | NULL       | eq_ref | PRIMARY                 | PRIMARY | 8       | test_db.pd.product_id,const                         |     1 |   100.00 | Using index                                |
|  1 | SIMPLE      | ad    | NULL       | ALL    | PRIMARY                 | NULL    | NULL    | NULL                                                |    46 |    10.00 | Using where; Using join buffer (hash join) |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY                 | PRIMARY | 4       | test_db.ad.attribute_id                             |     1 |   100.00 | Using index                                |
|  1 | SIMPLE      | pa    | NULL       | ref    | PRIMARY                 | PRIMARY | 12      | test_db.pd.product_id,test_db.ad.attribute_id,const |     1 |   100.00 | NULL                                       |
+----+-------------+-------+------------+--------+-------------------------+---------+---------+-----------------------------------------------------+-------+----------+--------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

CREATE TABLE `psy_product` (
  `product_id` int NOT NULL AUTO_INCREMENT,
  `model` varchar(64) NOT NULL,
  `sku` varchar(64) NOT NULL,
  `upc` varchar(12) NOT NULL,
  `ean` varchar(14) NOT NULL,
  `jan` varchar(13) NOT NULL,
  `isbn` varchar(17) NOT NULL,
  `mpn` varchar(64) NOT NULL,
  `location` varchar(128) NOT NULL,
  `quantity` int NOT NULL DEFAULT '0',
  `stock_status_id` int NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `manufacturer_id` int NOT NULL,
  `shipping` tinyint(1) NOT NULL DEFAULT '1',
  `price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `points` int NOT NULL DEFAULT '0',
  `tax_class_id` int NOT NULL,
  `date_available` date NOT NULL,
  `weight` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `weight_class_id` int NOT NULL DEFAULT '0',
  `length` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `width` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `height` decimal(15,8) NOT NULL DEFAULT '0.00000000',
  `length_class_id` int NOT NULL DEFAULT '0',
  `subtract` tinyint(1) NOT NULL DEFAULT '1',
  `minimum` int NOT NULL DEFAULT '1',
  `sort_order` int NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `viewed` int NOT NULL DEFAULT '0',
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `status` (`status`),
  KEY `quantity` (`quantity`)
) ENGINE=InnoDB AUTO_INCREMENT=46009 DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_attribute` (
  `attribute_id` int NOT NULL AUTO_INCREMENT,
  `attribute_group_id` int NOT NULL,
  `sort_order` int NOT NULL,
  PRIMARY KEY (`attribute_id`)
) ENGINE=MyISAM AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_attribute` (
  `product_id` int NOT NULL,
  `attribute_id` int NOT NULL,
  `language_id` int NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`product_id`,`attribute_id`,`language_id`,`text`(4)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_attribute_description` (
  `attribute_id` int NOT NULL,
  `language_id` int NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`attribute_id`,`language_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_description` (
  `product_id` int NOT NULL,
  `language_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `tag` text NOT NULL,
  `meta_title` varchar(255) NOT NULL,
  `meta_description` varchar(255) NOT NULL,
  `meta_keyword` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`,`language_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `psy_product_to_store` (
  `product_id` int NOT NULL,
  `store_id` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`,`store_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3