Monster Oasis
覺得累就是進步的開始!

2009/06/23

mysql index 的建立/使用 , Multiple-Column Indexes

Filed under: DATABASE — Tags: , , — 9:40 am

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

The name index is an index over the last_name and first_name columns. The index can be used for queries that specify values in a known range for last_name, or for both last_name and first_name. Therefore, the name index is used in the following queries: 

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

However, the name index is not used in the following queries, 以下的 query 用不到 index —> 多重 index 有先後區別. 

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';
Related URL:
  1. install oracle 11g steps
  2. 一些 memcache 的資料
  3. Protected: Oracle down time / problem records / 經典
  4. Database administrator
  5. 在 gentoo 裝 oracle sqlplus package
  6. Graphical Database Schema Metadata Browser – SchemaSpy
  7. wordpress 的 schema
  8. Oracle documentation library
  9. MySQL – Optimizing Database Structure
  10. Protected: 規格表 schema

Enter your password to view comments.


www.monster.com.tw , © Copyright 2008