Блог

У меня есть база данных MySQL с items таблицей, и каждый элемент привязан к учетной записи. Каждый из этих предметов может иметь один из нескольких цветов, что-то вроде этого:

Товары

 id | account_id | color
------------------------
1  | 1          | white
2  | 1          | white
3  | 1          | blue
4  | 1          | red
5  | 2          | white
6  | 2          | white
7  | 2          | white
 

На странице отображения элемента мы хотим показать цвет элемента, но только в том случае, если учетная запись когда-либо использовала более одного цвета. Как только они начнут использовать другой цвет в этой учетной записи, мы хотим показать цвета для всех элементов.

Например:

• При отображении элемента с идентификатором 1 , мы бы показали , что это так white , потому что в учетной записи есть элементы, которые представляют собой смесь белого, синего и красного.
• При отображении элемента с идентификатором 7 мы бы не показывали цвет , потому что все цвета в учетной записи одинаковы (белые), поэтому нет необходимости использовать цвет для их различения.

Чтобы сделать эту проверку, я в настоящее время выбираю любой элемент в учетной записи (первый, но он может быть любым) и запускаю запрос, чтобы узнать, есть ли в учетной записи элементы, которые не имеют такого же цвета, как этот:

 // Get any color in the account:
SELECT color FROM items WHERE account_id = 1 LIMIT 1;

// See if any other color is used ("red" was returned from the previous query):
SELECT 1 AS one FROM `items` WHERE account_id = 1 AND color != 'red' LIMIT 1;
 

Это работает и довольно эффективно для учетных записей, у которых есть множество цветов, которые они меняют, но в некоторых учетных записях могут быть тысячи или миллионы элементов, и они могут использовать только один цвет, поэтому, возможно, придется сканировать каждую строку в учетной записи, чтобы увидеть, нет ли других.

Я уже пытался добавить составной индекс в account_id и color , но даже для этого индекса пришлось бы сканировать тысячи или миллионы записей, чтобы быть уверенным, так как существует всего несколько цветов для группировки.

Is this acceptable? Is there a special kind of index I could use that would be efficient here, or would I need to add a trigger/procedure to update a cached column or table on every insert?

What I mean about triggers is something like this:

 DELIMITER $

CREATE TRIGGER items_after_insert_update_account_colors
AFTER INSERT
ON items FOR EACH ROW
BEGIN
  // Insert row, but ignore if the exact row already exists.
  INSERT IGNORE INTO account_colors
  SET color = NEW.color, account_id = NEW.account_id; 
END$

DELIMITER;
 

Затем проверьте эту таблицу, чтобы определить, следует ли отображать цвет или нет с помощью запроса, подобного этому:

 // True if other color is found for account.
SELECT 1 AS one FROM account_colors WHERE account_id = 1 AND color != 'red' LIMIT 1;
 

Существует ли метод индексирования, который я могу использовать для эффективной работы, или я использую триггер, подобный приведенному выше, для кэширования текущего состояния таблицы и обновления ее при каждой вставке-лучшее, что я могу сделать?

Подводя итог, я думаю, что спрашиваю, есть ли способ заставить индекс работать так, как описано выше, где даже для миллионов записей индекс крошечный и должен содержать только минимальную информацию, подобную этой:

 account_id | uses_color
-----------------------
1          | white
1          | blue
1          | red
2          | white
 

или

 account_id | used_colors
-------------------------------------
1          | ['blue', 'white', 'red']
2          | ['white']
 

Я бы предложил exists :

 select i.*
from items i
where exists (select 1
              from items i2
              where i2.account_id = i.account_id and
                    i2.color <> i.color
             )
order by i2.account_id;
 

Затем для производительности вам нужен индекс items(account_id, color) .

 SELECT COUNT(DISTINCT(col)) FROM table;
 

вернет 1, если в этом столбце есть только одно другое значение.

К WHERE нему можно прикрепить оговорку, чтобы каким-то образом ограничить его. И вы можете использовать результат в подзапросе.