SELECT A.CNO AS cNo, A.KNO AS kNo, A.ASSOCIATION AS association, A.AGROUP AS aGroup, A.CARDWRITER AS cardWriter, A.USERNAME AS userName, A.BIRTH AS birth, IF(LEFT(NOW(),10)>BIRTH,LEFT(NOW(),4)-LEFT(BIRTH,4),LEFT(NOW(),4)-LEFT(BIRTH,4)-1) AS AGE, A.PHONE1 AS phone1, A.PHONE2 AS phone2, A.PHONE3 AS phone3, B.SUBNAME AS subName, A.SENIORJOIN AS seniorJoin, A.ZIP AS zip, A.ADDR1 AS addr1, A.ADDR2 AS addr2, A.TEL1 AS tel1, A.TEL2 AS tel2, A.TEL3 AS tel3, A.SEX, CASE A.SEX WHEN 'M' THEN '남' WHEN 'F' THEN '여' ELSE A.SEX END GENDER, A.USERTYPE, CASE A.USERTYPE WHEN 'S' THEN '정회원' WHEN 'T' THEN '특별회원' ELSE A.USERTYPE END userTypeNm, A.DEMENTIA AS dementia, A.DEMENTIAGRADE AS dementiaGrade, A.DELYN AS delYn, A.WRID AS wrid, DATE_FORMAT(A.WRDT, '%Y-%m-%d') AS wrdt FROM users_card A LEFT JOIN sub_card B ON A.KNO = B.KNO AND B.DELYN = 'N' WHERE A.DELYN = 'N' ORDER BY CNO DESC
users_card 와 sub_card를 조인해서 select하는 쿼리입니다.
user_card에는 200만건
sub_card에는 6만건의 데이터가 들어있습니다.
인덱스는 검색되는 컬럼은 걸어줬습니다.
조건없이 총 검색을 하게되면 쿼리속도가 1분이 넘어가는데..
쿼리속도를 빠르게하고싶어서 질문남김니다..
어떤식으로 수정을 해야할지 답변좀 부탁드리겠습니다.