DB 쿼리 시간이 많이 걸려서 이런 내용을 받았는데 혹시 해석? 좀 해주실 수 있나요?

DB 쿼리 시간이 많이 걸려서 이런 내용을 받았는데 혹시 해석? 좀 해주실 수 있나요?

QA

DB 쿼리 시간이 많이 걸려서 이런 내용을 받았는데 혹시 해석? 좀 해주실 수 있나요?

본문

DB 쿼리 시간이 많이 걸려서 이런 내용을 받았는데 혹시 해석? 좀 해주실 수 있나요?

어디 DB 쿼리 때문에 로딩에 시간이 많이 걸리는걸까요..?

 

# Time: 191007 10:03:49
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641134  Schema: SITENAME  QC_hit: No
# Query_time: 4.641318  Lock_time: 0.626739  Rows_sent: 32  Rows_examined: 256
# Rows_affected: 0
use SITENAME;
SET timestamp=1570410229;
SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
              FROM mdl_course c
              JOIN (
                SELECT DISTINCT e.courseid
                  FROM mdl_enrol e
                  JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id AND ue.userid = '378')
                 WHERE ue.status = '0' AND e.status = '0' AND ue.timestart < 1570410200
                       AND (ue.timeend = 0 OR ue.timeend > 1570410200)) en ON (en.courseid = c.id)
           LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = '50')
             WHERE c.id <> '1'
          ORDER BY c.visible DESC,c.sortorder ASC;
# Time: 191007 10:03:50
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641194  Schema: SITENAME  QC_hit: No
# Query_time: 3.476462  Lock_time: 0.000030  Rows_sent: 1  Rows_examined: 8
# Rows_affected: 0
SET timestamp=1570410230;
SELECT  g.id, g.name, g.description, g.descriptionformat, g.idnumber
                                   FROM mdl_groups g , mdl_groups_members gm 
                                  WHERE g.courseid = '89' AND g.id = gm.groupid AND gm.userid = '353'
                               ORDER BY name ASC;
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641186  Schema: SITENAME  QC_hit: No
# Query_time: 3.720253  Lock_time: 0.000022  Rows_sent: 3  Rows_examined: 12
# Rows_affected: 0
SET timestamp=1570410230;
SELECT *
                                   FROM mdl_groups_members gm
                                   JOIN mdl_groups g
                                    ON g.id = gm.groupid
                                  WHERE gm.userid = '353'
                                   ORDER BY name ASC;
# Time: 191007 10:03:51
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641207  Schema: SITENAME  QC_hit: No
# Query_time: 3.341557  Lock_time: 0.000046  Rows_sent: 3  Rows_examined: 12
# Rows_affected: 0
SET timestamp=1570410231;
SELECT *
                                   FROM mdl_groups_members gm
                                   JOIN mdl_groups g
                                    ON g.id = gm.groupid
                                  WHERE gm.userid = '397'
                                   ORDER BY name ASC;
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641209  Schema: SITENAME  QC_hit: No
# Query_time: 3.522715  Lock_time: 0.000041  Rows_sent: 1  Rows_examined: 8
# Rows_affected: 0
SET timestamp=1570410231;
SELECT  g.id, g.name, g.description, g.descriptionformat, g.idnumber
                                   FROM mdl_groups g , mdl_groups_members gm 
                                  WHERE g.courseid = '44' AND g.id = gm.groupid AND gm.userid = '349'
                               ORDER BY name ASC;
# Time: 191007 10:03:53
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641235  Schema: SITENAME  QC_hit: No
# Query_time: 3.176174  Lock_time: 0.000104  Rows_sent: 32  Rows_examined: 256
# Rows_affected: 0
SET timestamp=1570410233;
SELECT c.id,c.category,c.sortorder,c.shortname,c.fullname,c.idnumber,c.startdate,c.visible,c.defaultgroupingid,c.groupmode,c.groupmodeforce,c.summary,c.summaryformat,c.format,c.showgrades,c.lang,c.enablecompletion,c.enddate , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
              FROM mdl_course c
              JOIN (SELECT DISTINCT e.courseid
                      FROM mdl_enrol e
                      JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id AND ue.userid = '331')
                 WHERE ue.status = '0' AND e.status = '0' AND ue.timestart < 1570410200 AND (ue.timeend = 0 OR ue.timeend > 1570410200)
                   ) en ON (en.courseid = c.id)
           LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = '50')
             WHERE c.id <> '1'
          ORDER BY c.visible DESC,c.sortorder ASC;
# User@Host: SITENAME[SITENAME] @ localhost []
# Thread_id: 1641186  Schema: SITENAME  QC_hit: No
# Query_time: 3.115000  Lock_time: 0.000084  Rows_sent: 32  Rows_examined: 256
# Rows_affected: 0
SET timestamp=1570410233;
SELECT c.* , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance
              FROM mdl_course c
              JOIN (
                SELECT DISTINCT e.courseid
                  FROM mdl_enrol e
                  JOIN mdl_user_enrolments ue ON (ue.enrolid = e.id AND ue.userid = '353')
                 WHERE ue.status = '0' AND e.status = '0' AND ue.timestart < 1570410200
                       AND (ue.timeend = 0 OR ue.timeend > 1570410200)) en ON (en.courseid = c.id)
           LEFT JOIN mdl_context ctx ON (ctx.instanceid = c.id AND ctx.contextlevel = '50')
             WHERE c.id <> '1'
          ORDER BY c.visible DESC,c.sortorder ASC;
 

이 질문에 댓글 쓰기 :

답변 2

해당 쿼리 그대로 긁어서 phpmyadmin 에서 실행후 SQL 해석 클릭하시면 걸리는 key 등을 확인하실 수 있습니다. mysql 옵티마이징에 대한 글들을 구글에서 검색하시어 인덱스 추가하시면 원하시는 결과 얻을수 있을것 같습니다.

답변을 작성하시기 전에 로그인 해주세요.
전체 840
QA 내용 검색
filter #DB ×

회원로그인

(주)에스아이알소프트 / 대표:홍석명 / (06211) 서울특별시 강남구 역삼동 707-34 한신인터밸리24 서관 1404호 / E-Mail: admin@sir.kr
사업자등록번호: 217-81-36347 / 통신판매업신고번호:2014-서울강남-02098호 / 개인정보보호책임자:김민섭(minsup@sir.kr)
© SIRSOFT