order by 시 속도 단축 방법 질문 드립니다.
테이블은 아래와 같습니다.
CREATE TABLE U_MEMBER (
MEMBER_SEQ INT(8) NOT NULL AUTO_INCREMENT,
MEMBER_ID VARCHAR(32) NOT NULL,
MEMBER_NAME VARCHAR(32) NOT NULL,
MEMBER_PASSWORD VARCHAR(24) NOT NULL,
STATION_CODE INT(8),
DEPT_CODE INT(8),
GRADE_CODE INT(8),
EMAIL VARCHAR(64),
PRIMARY KEY(MEMBER_SEQ, MEMBER_ID)
) ENGINE=MYISAM DEFAULT CHARSET=EUCKR;
CREATE TABLE U_GRADE (
GRADE_CODE VARCHAR(32) NOT NULL,
GRADE_NAME VARCHAR(32),
DISP_ORDER INT(4),
PRIMARY KEY(GRADE_CODE)
) ENGINE=MYISAM DEFAULT CHARSET=EUCKR;
CREATE TABLE U_DEPT (
DEPT_CODE VARCHAR(32) NOT NULL,
DEPT_NAME VARCHAR(64),
PRIMARY KEY(DEPT_CODE)
) ENGINE=MYISAM DEFAULT CHARSET=EUCKR;
U_MEMBER 테이블에 약 3만건의 데이터가 들어간 경우,
아래 질의를 하면 0.094 정도의 속도가 나옵니다.
SELECT
MEM.MEMBER_SEQ SEQ,
MEM.MEMBER_ID ID,
MEM.MEMBER_NAME NAME,
GRADE.GRADE_NAME,
MEM.EMAIL EMAIL
FROM
U_MEMBER MEM,
U_GRADE GRADE
WHERE
MEM.MEMBER_ID = MEM.MEMBER_ID
AND MEM.GRADE_CODE = GRADE.GRADE_CODE
ORDER BY MEM.MEMBER_NAME ASC LIMIT 0, 6;
index는 아래와 같습니다. 걸어준게 없죠.
이렇게 저렇게 걸어줘도 속도가 나오지 않아서.
직위도 그렇고, 부서도 나중에 select에 추가해야 되는데 속도를 좀더 빨리 할 수는 없는지.
부탁드립니다.
mysql> SHOW INDEX FROM U_MEMBER;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| U_MEMBER | 0 | PRIMARY | 1 | MEMBER_SEQ | A | NULL | NULL | NULL | | BTREE | |
| U_MEMBER | 0 | PRIMARY | 2 | MEMBER_ID | A | 30000 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> show INDEX FROM U_GRADE;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part| Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| U_GRADE | 0 | PRIMARY | 1 | GRADE_CODE | A | 5 | NULL| NULL | | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec) |