Оптимизация подзапроса SQL через LEFT JOIN

Я хочу вставить все записи из таблицы factEntries в таблицу uniqueEntries на основе идентификаторов User_ID, которые не существуют в uniqueEntries.

Я начал с предложения sql, содержащего подзапрос NOT IN, который очень медленный (при работе с записями 400 КБ), и превратил его в предложение LEFT JOIN, но скорость не улучшилась.

Ниже приведено мое исходное предложение sql, содержащее подзапрос NOT IN:

INSERT INTO uniqueEntries 
  SELECT * 
  FROM actualEntries 
  WHERE actualEntries.User_ID NOT IN (
    SELECT uniqueEntries.User_ID 
    FROM uniqueEntries
  )
  GROUP BY User_ID"

Ниже приведено предложение sql после преобразования в LEFT JOIN:

INSERT INTO uniqueEntries 
  SELECT actualEntries.* 
  FROM actualEntries 
  LEFT JOIN uniqueEntries 
  ON uniqueEntries.User_ID = actualEntries.User_ID 
  WHERE uniqueEntries.User_ID IS NULL 
  GROUP BY User_ID

Когда я запускаю оба запроса для 50 записей, они завершаются сразу, но когда я запускаю их для 400 000 записей, они не завершаются.

Как быстрее всего сделать эту операцию?

ОБНОВЛЕНИЕ / РЕШЕНИЕ. Согласно @Rahul, @Steve E и @fhthiella, я обновил LEFT JOIN следующим образом и сократил время обработки до 2 минут для 470 тыс. записей.

INSERT INTO uniqueEntries 
  SELECT actualEntries.* 
  FROM actualEntries 
  LEFT JOIN uniqueEntries 
  ON uniqueEntries.id = actualEntries.id 
  WHERE uniqueEntries.User_ID IS NULL GROUP BY User_ID

mysql join sql-insert subquery notin
person xited    schedule 16.09.2015    source источник

Ответы (3)


arrow_upward
1
arrow_downward

Прежде всего удалите пункт GROUP BY GROUP BY User_ID, так как он вообще не нужен. Кроме того, у вас должен быть индекс в столбце User_ID для обеих таблиц uniqueEntries и actualEntries, поскольку вы используете его в качестве столбца соединения. при этом ваш запрос должен выглядеть так

INSERT INTO uniqueEntries 
  SELECT actualEntries.* 
  FROM actualEntries 
  LEFT JOIN uniqueEntries 
  ON uniqueEntries.User_ID = actualEntries.User_ID 
  WHERE uniqueEntries.User_ID IS NULL
person Rahul    schedule 16.09.2015
comment
Я изменил .User_ID для uniqueEntries и factEntries на .id (это работает). Мне нужна GROUP BY, потому что некоторые записи дублируются, а мне нужны только уникальные записи. - person xited; 16.09.2015
comment
@xited, тем не менее я считаю, что вам не нужно GROUP BY, потому что оператор where WHERE uniqueEntries.User_ID IS NULL будет делать то, что вы говорите. - person Rahul; 16.09.2015
comment
@Rahul не совсем sqlfiddle.com/#!9/096ed8/1 в этом контексте группа по все еще необходима (или лучше использовать первичный ключ, а затем использовать INSERT IGNORE sqlfiddle.com /#!9/e3131/1 - person fthiella; 16.09.2015
comment
@Rahul: uniqueEntries не содержит несколько записей с одним и тем же User_ID, а factEntries содержит. GROUP BY должен гарантировать, что я извлекаю только уникальные записи из фактических записей после удаления уникальных записей из вывода, верно? - person xited; 16.09.2015
comment
@xited, тогда да, вам может понадобиться группа; но я бы сначала получил результат группировки, используя group by, а затем выполнил соединение с unique_entries. В противном случае используйте конструкцию insert ignore .... - person Rahul; 16.09.2015

arrow_upward
2
arrow_downward

Поместите уникальный или первичный ключ в uniqueEntries.User_ID. затем

INSERT IGNORE INTO uniqueEntries 
  SELECT actualEntries.* 
  FROM actualEntries

Предложение IGNORE заставит MySQL пропустить ошибки в процессе вставки. Вот что говорится в руководстве:

Если вы используете ключевое слово IGNORE, ошибки, возникающие при выполнении оператора INSERT, игнорируются. Например, без IGNORE строка, которая дублирует существующий индекс UNIQUE или значение PRIMARY KEY в таблице, вызывает ошибку дублирования ключа, и оператор прерывается. При использовании IGNORE строка отбрасывается, и ошибка не возникает. Игнорируемые ошибки могут вместо этого генерировать предупреждения, а ошибки с дублированием ключа — нет.

person Steve E.    schedule 16.09.2015
comment
Insert Ignore частично помог с моим запросом. - person xited; 06.10.2015

arrow_upward
1
arrow_downward

Вы должны добавить индекс для полей uniqueEntries.User_ID и factEntries.User_ID:

ALTER TABLE uniqueEntries ADD INDEX idx_ue_id (User_ID);
ALTER TABLE actualEntries ADD INDEX idx_ae_id (User_ID);

и это должно сделать соединение намного быстрее. Я также вижу, что вы выбираете все поля таблицы:

SELECT actualEntries.*

но тогда вы группируете по User_id

GROUP BY User_ID

Я думаю, вы делаете это, потому что для каждого User_ID может быть несколько строк. MySQL позволяет вам сделать это, но обратите внимание, что если есть несколько строк, ваш запрос сохранит только одну, но значения, которые не сгруппированы, будут неопределенными (они могут принадлежать любой из сгруппированных строк).

person fthiella    schedule 16.09.2015
comment
@fhthiella несколько строк. Это правильно, и мне все равно, какой из них будет выбран, пока я получаю уникальные строки. - person xited; 16.09.2015
comment
@xited хорошо, так что вы можете использовать GROUP BY таким образом, если вы знаете, что делаете;) но если вы хотите, чтобы User_ID уникальных записей был уникальным, я думаю, вы должны принять ответ steve e, поскольку он более элегантный! - person fthiella; 16.09.2015