SQLite3でのインデックススキーマについて
auです。
インデックスの構造(インデックススキーマ)を確認したいと思います。
インデックススキーマの確認
sqlite_masterコマンドを使って確認してみます。
SELECT * FROM sqlite_master; type = table name = book tbl_name = book rootpage = 2 sql = CREATE TABLE book(id, name) type = table name = book2 tbl_name = book2 rootpage = 3 sql = CREATE TABLE book2(id integer, name text) type = table name = member tbl_name = member rootpage = 4 sql = CREATE TABLE member(id integer primary key, name text) type = table name = sqlite_sequence tbl_name = sqlite_sequence rootpage = 7 sql = CREATE TABLE sqlite_sequence(name,seq) type = view name = customer tbl_name = customer rootpage = 0 sql = CREATE VIEW customer AS SELECT id, name FROM user type = view name = customer2 tbl_name = customer2 rootpage = 0 sql = CREATE VIEW customer2 AS SELECT id, name FROM user WHERE id >= 2 type = table name = user tbl_name = user rootpage = 5 sql = CREATE TABLE user(id integer unique, name, address) type = index name = sqlite_autoindex_user_1 tbl_name = user rootpage = 6 sql = type = table name = staff tbl_name = staff rootpage = 8 sql = CREATE TABLE staff(id integer, name text, address text, UNIQUE(id, name)) type = index name = sqlite_autoindex_staff_1 tbl_name = staff rootpage = 9 sql =
type=indexになっている、最後のがindexになっているのが確認できます。
インデックスだけに検索を絞る
WHERE文で検索範囲を指定することができます。
SELECT * FROM sqlite_master WHERE type='index'; type = index name = sqlite_autoindex_user_1 tbl_name = user rootpage = 6 sql = type = index name = sqlite_autoindex_staff_1 tbl_name = staff rootpage = 9 sql =