我的产品有一个eav模型，它将产品的属性存储在mysql数据库中。
我的问题是，如何有效地检索属性的特定计数（包括按所述属性过滤产品时）？
我已经将示例模式放在底部或sql fiddles中
更新的问题（以前的表格很乱）
我特别需要的是，当过滤这个结果集时，比如说，用4gb内存过滤谷歌产品，得到的结果是：

+--------------+-------------+-------+------------------------------------------+
| attributeId  |    value    | count |                                                                                                 |
+--------------+-------------+-------+------------------------------------------+
| 1            | Google      |     3 | Google with 4GB RAM                                                                             |
| 1            | Apple       |     2 | Apple with 4GB RAM (for active filters, show all options that match only the other filters)     |
| 1            | Microsoft   |     1 | Microsoft with 4GB RAM (for active filters, show all options that match only the other filters) |
| 1            | Sony        |     1 | Sony with 4GB RAM (for active filters, show all options that match only the other filters)      |
| 2            | Phone       |     1 | 1 Phone with 4GB RAM (matching both active filters)                                             |
| 3            | 4GB         |     3 | Even though it's an active filter, all Google products have 4GB RAM                             |
| 4            | Mali 200    |     1 |                                                                                                 |
| 4            | GMA 650     |     1 |                                                                                                 |
| 5            | Black       |     2 |                                                                                                 |
| 5            | Gray        |     1 |                                                                                                 |
| availability | unavailable |     3 |                                                                                                 |
| price        | > 250       |     2 |                                                                                                 |
| price        | 0-50        |     1 |                                                                                                 |
+--------------+-------------+-------+------------------------------------------+

总而言之，如果属性是按筛选的，则需要显示该属性的所有值，这些值的计数与所有其他（n-1）筛选器匹配。
如果未按筛选，则需要显示与所有n个筛选器匹配的计数。
另外，产品表中还有两个特殊属性（可用性和价格）需要处理。目前正在为这些人建立一个联盟。
例子：

Attributes: A, B, C, D ,E, F
Filters: A, C, D
Result:
A (Active Filter) - Values with counts that match filters C, D
B - Values with counts that match filters A, C, D
C (Active Filter) - Values with counts that match filters A, D
D (Active Filter) - Values with counts that match filters C, D
E - Values with counts that match filters A, C, D
F - Values with counts that match filters A, C, D

我当前使用的查询是：
更新稍微改变了以前的查询，现在它给出了正确的结果，但是它仍然是相当慢，我不确定我想去生产与此；

(
    SELECT 
        'availability' AS attributeId, 
        availability as value,
      count(1) as 'count'
   FROM (
        SELECT p.id, p.availability FROM Products p
        JOIN ProductAttributes pa ON
            pa.productId = p.id
        WHERE
            (pa.attributeId = 1 AND pa.value = 'A1-Value A')
        GROUP BY p.id, p.availability
        HAVING (
            COUNT(*) = 1
        )
    ) AS pf
    GROUP BY availability
)
UNION
(
    SELECT 
        'price' AS attributeId, 
        case 
        when (price) between 0 and 50 then '0-50'
        when (price) between 50 and 100 then '51-100'
        when (price) between 100 and 150 then '101-150'
        when (price) between 150 and 200 then '151-200'
        when (price) between 200 and 250 then '201-250'
      ELSE '> 250'
      end as value,
      count(1) as 'count'
   FROM (
        SELECT p.id, p.price FROM Products p
        JOIN ProductAttributes pa ON
            pa.productId = p.id
        WHERE
            (pa.attributeId = 1 AND pa.value = 'A1-Value A')
        GROUP BY p.id, p.price
        HAVING (
            COUNT(*) = 1
        )
    ) AS pf
    GROUP BY value
)   
UNION
(
    SELECT 
        pfa.attributeId, pfa.value, COUNT(*)
    FROM (
            SELECT p.id FROM Products p
            JOIN ProductAttributes pa ON
                pa.productId = p.id
            GROUP BY p.id
        ) AS pf
         JOIN ProductAttributes pfa ON
            pfa.productId = pf.id
         WHERE (
            NOT (pfa.attributeId = 1 AND pfa.value = 'A1-Value A')
         )
    GROUP BY pfa.attributeId, pfa.value
)
UNION
(
    SELECT 
        pfa.attributeId, pfa.value, COUNT(*)
    FROM (
            SELECT p.id FROM Products p
            JOIN ProductAttributes pa ON
                pa.productId = p.id
            WHERE
                (pa.attributeId = 1 AND pa.value = 'A1-Value A')
            GROUP BY p.id
            HAVING (
                COUNT(*) = 1
            )
        ) AS pf
         JOIN ProductAttributes pfa ON
            pfa.productId = pf.id
         WHERE
         (
            (pfa.attributeId = 1 AND pfa.value = 'A1-Value A')
         )

    GROUP BY pfa.attributeId, pfa.value
)

sql小提琴
然而，它并没有完全返回正确的结果，它有点笨重，我希望有人知道如何正确地做到这一点。
谢谢！
示例架构

CREATE TABLE `Products` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `categoryId` INT(11) NULL DEFAULT NULL,
    `name` VARCHAR(255) NOT NULL,
    `availability` VARCHAR(255) NOT NULL,
    `price` FLOAT NOT NULL,
    `discount` FLOAT NULL DEFAULT NULL,
    `active` INT(11) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `price` (`price`),
    INDEX `categoryId` (`categoryId`),
    INDEX `discount` (`discount`),
    FULLTEXT INDEX `name` (`name`)
);

INSERT INTO `Products` (`id`, `categoryId`, `name`, `availability`, `price`, `discount`, `active`)
VALUES ('1', '1', 'Stadia', 'unavailable', '50', '4.06', true),
('2', '1', 'Pixel 3', 'available', '500', '4.06', true),
('3', '1', 'Chromebook', 'unavailable', '300', '4.06', true),
('4', '1', 'Mac Pro', 'unavailable', '1200', '4.06', true),
('5', '1', 'iPhone', 'unavailable', '1000', '4.06', true),
('6', '1', 'iPad', 'available', '400', '4.06', true),
('7', '1', 'Playstation 4', 'unavailable', '400', '4.06', true),
('8', '1', 'Xperia Phone', 'unavailable', '300', '4.06', true),
('9', '1', 'Surface Pro', 'available', '800', '4.06', true),
('10', '1', 'Windows Phone 7 Phone', 'unavailable', '300', '4.06', true),
('11', '1', 'Windows 10 PC', 'unavailable', '2000', '4.06', true),
('12', '1', 'XBOX', 'available', '500', '4.06', true);

CREATE TABLE `Attributes` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `textId` VARCHAR(255) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO `Attributes` (`id`, `textId`, `name`)
VALUES ('1', 'Brand', 'brand'),
('2', 'Type','type'),
('3', 'RAM', 'ram'),
('4', 'GPU', 'gpu'),
('5', 'Color', 'color');

CREATE TABLE `ProductAttributes` (
    `attributeId` INT(11) NOT NULL,
    `productId` INT(11) NOT NULL,
    `value` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`attributeId`, `productId`),
    INDEX `ValueIndex` (`value`),
    INDEX `AttrIndex` (`attributeId`)
);

INSERT INTO `ProductAttributes` (`attributeId`, `productId`, `value`)
VALUES 
('1', '1','Google'),
('1', '2', 'Google'),
('1', '3', 'Google'),
('1', '4', 'Apple'),
('1', '5', 'Apple'),
('1', '6', 'Apple'),
('1', '7', 'Sony'),
('1', '8', 'Sony'),
('1', '9', 'Microsoft'),
('1', '10', 'Microsoft'),
('1', '11', 'Microsoft'),
('1', '12', 'Microsoft'),
('2', '1','Game Console'),
('2', '2', 'Phone'),
('2', '3', 'Computer'),
('2', '4', 'Computer'),
('2', '5', 'Phone'),
('2', '6', 'Tablet'),
('2', '7', 'Game Console'),
('2', '8', 'Phone'),
('2', '9', 'Tablet'),
('2', '10', 'Phone'),
('2', '11', 'Computer'),
('2', '12', 'Game Console'),
('3', '1', '4GB'),
('3', '2', '4GB'),
('3', '3', '4GB'),
('3', '4', '6GB'),
('3', '5', '4GB'),
('3', '6', '4GB'),
('3', '7', '8GB'),
('3', '8', '4GB'),
('3', '9', '6GB'),
('3', '10', '4GB'),
('3', '11', '8GB'),
('3', '12', '8GB'),
('4', '2', 'Mali 200'),
('4', '3', 'GMA 650'),
('4', '4', 'GMA 650'),
('4', '5', 'Mali 400'),
('4', '6', 'Mali 200'),
('4', '7', 'Radeon'),
('4', '8', 'Mali 400'),
('4', '9', 'GMA 650'),
('4', '10', 'Mali 400'),
('4', '11', 'Nvidia'),
('4', '12', 'Radeon'),
('5', '1','Black'),
('5', '2', 'Black'),
('5', '3', 'Gray'),
('5', '4', 'Gray'),
('5', '5', 'Black'),
('5', '6', 'Black'),
('5', '7', 'Black'),
('5', '8', 'Blue'),
('5', '9', 'Gray'),
('5', '10', 'Blue'),
('5', '11', 'Black'),
('5', '12', 'Green');