IT story

MySQL-SELECT WHERE field IN (하위 쿼리)-왜 느리게?

hot-time 2020. 7. 13. 07:56
반응형

MySQL-SELECT WHERE field IN (하위 쿼리)-왜 느리게?

검사하려는 데이터베이스에 두 개의 중복 항목이 있으므로 중복 항목을 확인하기 위해 수행 한 작업은 다음과 같습니다.

SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1

이렇게하면 related_field가있는 모든 행을 두 번 이상 가져옵니다. 이 쿼리는 실행하는 데 밀리 초가 걸립니다.

이제 각 중복 항목을 검사하고 싶었으므로 위 쿼리에서 related_field를 사용하여 some_table의 각 행을 선택할 수 있다고 생각했습니다.

SELECT *
FROM some_table 
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)

이것은 어떤 이유로 외부 적으로 느리게 나타납니다 (분이 걸립니다). 그것을 느리게하기 위해 여기서 정확히 무엇을하고 있습니까? related_field가 색인됩니다.

결국 첫 번째 query에서 "temp_view"보기 (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1)를 만든 다음 두 번째 쿼리를 다음과 같이 만들려고했습니다.

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM temp_view
)

그리고 그것은 잘 작동합니다. MySQL은 몇 밀리 초 안에이를 수행합니다.

무슨 일이 일어나고 있는지 설명 할 수있는 SQL 전문가가 있습니까?

이 질문을 다시 작성하십시오

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

st2.relevant_field그렇지 않으면 having절에 오류가 발생 하기 때문에 선택에 있어야 한다고 생각 하지만 100 % 확신 할 수는 없습니다.

IN하위 쿼리와 함께 사용하지 마십시오 . 이것은 매우 느립니다. 고정 된 값 목록
만 사용하십시오 IN.

더 많은 팁

  1. 쿼리 속도를 높이려면 SELECT *실제로 필요한 필드 만 선택 하지 마십시오 .
  2. relevant_field동등 조인 속도를 높이려면 인덱스가 있어야합니다 .
  3. group by기본 키 를 확인하십시오 .
  4. 당신은 이노에있는 경우 와 만 인덱스 필드를 선택 (그리고 상황이 너무 복잡하지 않은) 의 MySQL은 인덱스를 사용하여 쿼리를 해결할 수보다 일의 길을 속도.

IN (select쿼리의 90 %를위한 일반 솔루션

이 코드를 사용하십시오

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

하위 쿼리는 상관 된 쿼리이므로 각 행에 대해 실행됩니다. 하위 쿼리에서 다음과 같이 모든 항목을 선택하여 상관 쿼리를 상관되지 않은 쿼리로 만들 수 있습니다.

SELECT * FROM
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) AS subquery

최종 쿼리는 다음과 같습니다.

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT * FROM
    (
        SELECT relevant_field
        FROM some_table
        GROUP BY relevant_field
        HAVING COUNT(*) > 1
    ) AS subquery
)

하위 쿼리와 조인

http://www.scribd.com/doc/2546837/New-Subquery-Optimizations-In-MySQL-6

www.prettysql.net으로 느린 SQL 쿼리를 다시 포맷했습니다.

SELECT *
FROM some_table
WHERE
 relevant_field in
 (
  SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT ( * ) > 1
 );

쿼리와 하위 쿼리 모두에서 테이블을 사용하는 경우 항상 다음과 같이 별칭을 지정해야합니다.

SELECT *
FROM some_table as t1
WHERE
 t1.relevant_field in
 (
  SELECT t2.relevant_field
  FROM some_table as t2
  GROUP BY t2.relevant_field
  HAVING COUNT ( t2.relevant_field ) > 1
 );

도움이 되나요?

SELECT st1.*
FROM some_table st1
inner join 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)st2 on st2.relevant_field = st1.relevant_field;

내 데이터베이스 중 하나에서 쿼리를 시도했으며 하위 쿼리에 대한 조인으로 다시 작성했습니다.

이것은 훨씬 빨리 작동했습니다. 사용해보십시오!

이 시도

SELECT t1.*
FROM 
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;

sometimes when data grow bigger mysql WHERE IN's could be pretty slow because of query optimization. Try using STRAIGHT_JOIN to tell mysql to execute query as is, e.g. 

SELECT STRAIGHT_JOIN table.field FROM table WHERE table.id IN (...)

but beware: in most cases mysql optimizer works pretty well, so I would recommend to use it only when you have this kind of problem

Firstly you can find duplicate rows and find count of rows is used how many times and order it by number like this;

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

after that create a table and insert result to it.

create table CopyTable 
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

Finally, delete dublicate rows.No is start 0. Except fist number of each group delete all dublicate rows. 

delete from  CopyTable where No!= 0;

This is similar to my case, where I have a table named tabel_buku_besar. What I need are

  1. Looking for record that have account_code='101.100' in tabel_buku_besar which have companyarea='20000' and also have IDR as currency

  2. I need to get all record from tabel_buku_besar which have account_code same as step 1 but have transaction_number in step 1 result

while using select ... from...where....transaction_number in (select transaction_number from ....), my query running extremely slow and sometimes causing request time out or make my application not responding...

I try this combination and the result...not bad...

`select DATE_FORMAT(L.TANGGAL_INPUT,'%d-%m-%y') AS TANGGAL,
      L.TRANSACTION_NUMBER AS VOUCHER,
      L.ACCOUNT_CODE,
      C.DESCRIPTION,
      L.DEBET,
      L.KREDIT 
 from (select * from tabel_buku_besar A
                where A.COMPANYAREA='$COMPANYAREA'
                      AND A.CURRENCY='$Currency'
                      AND A.ACCOUNT_CODE!='$ACCOUNT'
                      AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) L 
INNER JOIN (select * from tabel_buku_besar A
                     where A.COMPANYAREA='$COMPANYAREA'
                           AND A.CURRENCY='$Currency'
                           AND A.ACCOUNT_CODE='$ACCOUNT'
                           AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) R ON R.TRANSACTION_NUMBER=L.TRANSACTION_NUMBER AND R.COMPANYAREA=L.COMPANYAREA 
LEFT OUTER JOIN master_account C ON C.ACCOUNT_CODE=L.ACCOUNT_CODE AND C.COMPANYAREA=L.COMPANYAREA 
ORDER BY L.TANGGAL_INPUT,L.TRANSACTION_NUMBER`

I find this to be the most efficient for finding if a value exists, logic can easily be inverted to find if a value doesn't exist (ie IS NULL);

SELECT * FROM primary_table st1
LEFT JOIN comparision_table st2 ON (st1.relevant_field = st2.relevant_field)
WHERE st2.primaryKey IS NOT NULL

*Replace relevant_field with the name of the value that you want to check exists in your table

*Replace primaryKey with the name of the primary key column on the comparison table.

참고URL : https://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why

반응형

'IT story' 카테고리의 다른 글

Android Studio IDE : 예외 발생시 중단  (0) 2020.07.13
스위프트 : nil에 대한 옵션 테스트  (0) 2020.07.13
부트 스트랩 반응 형 페이지에서 div를 가운데에 배치하는 방법  (0) 2020.07.13
Jackson에서 Java 객체를 JsonNode로 변환  (0) 2020.07.13
로컬 phpMyAdmin 클라이언트로 원격 서버에 액세스하는 방법은 무엇입니까?  (0) 2020.07.13

'IT story'의 다른글

  • 현재글MySQL-SELECT WHERE field IN (하위 쿼리)-왜 느리게?

관련글

댓글

티스토리툴바