[mySQL5.5] 10장. FullText & Row Storage Format등

n  Other Storage Engines

n   Full Text 인덱스
-. an index of type FULLTEXT.
-. can be used only with InnoDB or MyISAM
-. can be created only for CHAR, VARCHAR, or TEXT columns.
-. performed using MATCH() ... AGAINST syntax

 

1.     먼저 실습을 위한 테이블을 생성합니다.

  mysql> use test;

mysql> CREATE TABLE articles (

      id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

      title VARCHAR(200),

      body TEXT,

      FULLTEXT (title,body)

    ) ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO articles (title,body)

VALUES

    ('MySQL Tutorial','DBMS stands for DataBase ...'),

    ('How To Use MySQL Well','After you went through a ...'),

    ('Optimizing MySQL','In this tutorial we will show ...'),

    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

    ('MySQL vs. YourSQL','In the following database comparison ...'),

    ('MySQL Security','When configured properly, MySQL ...');

Query OK, 6 rows affected (0.00 sec)

2. Full text인덱스에서 match()검색을 해 봅니다.

mysql> SELECT * FROM articles

    WHERE MATCH (title,body)

    AGAINST ('database' IN NATURAL LANGUAGE MODE);

 

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

 

n  Spatial Data Types

GEOMETRY

POINT

LINESTRING

POLYGON

1.     실습을 위한 테이블을 생성합니다.

mysql>  CREATE TABLE geom (g GEOMETRY);

2.     데이터를 loading합니다.

mysql>

INSERT INTO geom VALUES (GeomFromText('POINT(1 1)'));

mysql>

SET @g = 'LINESTRING(0 0,1 1,2 2)';

INSERT INTO geom VALUES (GeomFromText(@g));

mysql>

SET @g = 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';

INSERT INTO geom VALUES (GeomFromText(@g));

mysql>

SET @g ='GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4))';

INSERT INTO geom VALUES (GeomFromText(@g));

 

3.     데이터를 조회합니다.

mysql> SELECT AsText(g) FROM geom;

+----------------------------------------------------------------+

| AsText(g)                                                      |

+----------------------------------------------------------------+

| POINT(1 1)                                                     |

| LINESTRING(0 0,1 1,2 2)                                        |

| POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))       |

| GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(0 0,1 1,2 2,3 3,4 4)) |

+----------------------------------------------------------------+

4 rows in set (0.00 sec)

 

• MyISAM : Row Storage Format
  - row_format =dynamic인 경우
  - row length 계산 식 =

3

+ (number of columns + 7) / 8

+ (number of char columns)

+ (packed size of numeric columns)

+ (length of strings)

+ (number of NULL columns + 7) / 8

 

n   MyISAM:Locking Piority
-. LOW_PRIORITY: DML, read-heavy environment ,모든 waiting SELECT 가 완료된 이후에 DML을 수행합니다.
    INSERT LOW_PRIORITY INTO tbl_name (a,b,c)
           VALUES(1,2,3),(4,5,6),(7,8,9);
-. HIGH_PRIORITY : SELECT, 빨리 끝나는 쿼리에 사용해야함, 대기중이 DML보다 우선적으로 SELECT문을 수행합니다.

        SELECT HIGH_PRIORITY ….;