Оптимизиране на SQL подзаявка чрез LEFT JOIN

Искам да вмъкна всички записи от таблицата actualEntries в таблицата uniqueEntries въз основа на User_IDs на actualEntries, които не съществуват в uniqueEntries.

Започнах с sql клауза, съдържаща NOT IN подзаявка, която е много бавна (при работа с 400K записа) и я превърнах в 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 записа, те завършват веднага, но когато ги пусна на 400K записа, те не завършват.

Какъв е най-бързият начин за извършване на тази операция?

АКТУАЛИЗАЦИЯ / РЕШЕНИЕ: Според @Rahul, @Steve E и @fhthiella актуализирах LEFT JOIN както следва и намалих времето за обработка до 2 минути за 470K записа.

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 и actualEntries на .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, но actualEntries съдържа. GROUP BY трябва да гарантира, че тегля само уникални записи от actualEntries след премахване на uniqueEntries от изхода, нали? - 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
Вмъкване Игнориране помогна частично с молбата ми. - person xited; 06.10.2015

arrow_upward
1
arrow_downward

Трябва да добавите индекс към двете полета uniqueEntries.User_ID и actualEntries.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 на uniqueEntries да е уникален, мисля, че трябва да приемете steve e отговора, тъй като е по-елегантно! - person fthiella; 16.09.2015