ORDER BY 구문을 이용할 때 아래와 같이 order 방식을 다르게 SELECT를 할 필요가 있는 경우가 있다.

SELECT *
FROM tablename
ORDER BY A DESC, B ASC, C ASC

위와 같이 쿼리를 날리게되면, index를 만들 때 A,B,C 컬럼에 composit index가 생성이 되어 있더라도, 정렬이 모두 DESC나 ASC로 되어 있다면 결국 index를 제대로 활용 못하고 filesort가 나게 된다.

도움말을 찾아보면 아래와 같이 INDEX 생성 시점에 columnname [ASC | DESC] 와 같이 정렬 순서를 컬럼별로 줄 수 있다고 써있다.



CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
[index_type] 
ON tbl_name (index_col_name,...) 
[index_option ...] 

index_col_name: 
col_name [(length)] [ASC | DESC] 

index_type: 
USING {BTREE | HASH | RTREE} 

index_option: 
KEY_BLOCK_SIZE value | index_type | WITH PARSER parser_name


그래서 실제로 적용해 봤는데, 그래도 계속 filesort가 발생해서 도움말을 좀 더 읽어봤더니... 역시나 --;;

An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

앞으로 추가될 기능이라는 얘기가 있었다. parse는 잘 되지만 무시한다고 --;;

'programming > MySQL' 카테고리의 다른 글

drop table 문과 transaction  (0) 2011.03.02
mysql을 잘 설치하고 잘 띄우기  (0) 2009.06.01
Query Optimize를 제대로 못하는 mysql 길들이는 7가지 방법  (0) 2009.04.30
mysql index관련 검색을 하다 재미있는 내용의 글을 발견 정리하려고 올립니다.
mysql의 경우에도 버젼업이 많이 되고 있어, 최신 버젼의 optimizer에 대해서 아는바가 없지만, 현재 4.x대를 사용 중이라 optimizer의 문제를 너무 많이 느끼고 있어서 공감했던 글입니다.


위 글의 내용을 정리하면 대략 다음과 같습니다.

CREATE TABLE t (
  id INT UNSIGNED AUTO_INCREMENT,
  type INT UNSIGNED,
  level TINYINT unsigned,
  ...
  PRIMARY KEY(id),
  KEY `type` (type)
) ENGINE=InnoDB;

위와 같은 테이블이 있고, 해당 테이블에 대해 아래와 같은 쿼리를 사용하려고 합니다.

SELECT id FROM data
WHERE type=12345 AND level > 3
ORDER BY id

정확한 이해를 위해 보다 자세한 내용을 소개하자면

  • t라는 테이블에는 약 10,000,000개의 row가 있다.
  • 'type'이라는 index의 선택도는 대략 하나의 value에 대해 평균 100row정도 (예를 들어 type 'x'를 검색하면 결과가 평균 100개정도 나온다는 의미)
  • 쿼리 수행 시간이 오래 걸린다.
  • EXPLAIN 결과에서 보면 key는 primary key를 이용한다고 하고 rows는 10,000,000, extra 정보에는 using where라고 나온다.
  • 'type' index를 이용한 EXPLAIN 결과에서는 rows는 110rows, extra정보에는 "using where;using filesort"라고 나온다.

실제 실행 시간은 mysql이 제시한 방법 보다는 'type' index를 이용한 쿼리가 나은 결과를 보이는데, 그럼 쿼리를 어떻게 고치면 'type' index를 사용할 수 있게 할 수 있는지에 대해 7가지 방법에 대해 소개를 하고 있습니다.

몰랐던 방법들도 있네요.
내용이 어렵진 않으니 원문을 참고하세요~

'programming > MySQL' 카테고리의 다른 글

mysql을 잘 설치하고 잘 띄우기  (0) 2009.06.01
MySQL unique index생성 시 size 제한  (0) 2009.02.17
mysql의 'ON DUPLICATE KEY UPDATE' 구문  (4) 2007.08.27

+ Recent posts

티스토리툴바