SQLiteで単一列選択を高速化する多重列インデックスの使い方

2024-04-02

SQLiteにおける多重列インデックスと単一列選択

インデックスの種類

単一列インデックス: 1つの列に基づいてデータの検索を高速化します。

単一列選択と多重列インデックス

単一列選択の場合でも、多重列インデックスが有効な場合があります。

WHERE句で複数の列を指定する場合: WHERE句で指定した列がすべて多重列インデックスに含まれている場合、インデックスが使用されます。

例

次の例では、customersテーブルにname列とage列があり、name列とage列で構成される多重列インデックスが作成されています。

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER
);

CREATE INDEX idx_name_age ON customers (name, age);

次のクエリは、name列に基づいてデータを取得します。

SELECT * FROM customers WHERE name = 'John Doe';

この場合、idx_name_ageインデックスが使用されます。

SELECT * FROM customers WHERE age = 30;

SELECT * FROM customers WHERE name = 'John Doe' AND age = 30;

単一列選択の場合でも、多重列インデックスが有効な場合があります。WHERE句やORDER BY句で複数の列を指定する場合、インデックスが使用される可能性があります。

-- テーブル作成
CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  age INTEGER
);

-- 多重列インデックス作成
CREATE INDEX idx_name_age ON customers (name, age);

-- 単一列選択 (name)
SELECT * FROM customers WHERE name = 'John Doe';

-- 単一列選択 (age)
SELECT * FROM customers WHERE age = 30;

-- 複数列選択 (name, age)
SELECT * FROM customers WHERE name = 'John Doe' AND age = 30;

-- ORDER BY 複数列
SELECT * FROM customers ORDER BY name, age;

補足

サンプルコードはSQLite 3.35.5で動作確認済みです。
インデックスの効果は、データ量やクエリパターンによって異なります。

単一列選択のためのその他の方法

単一列インデックスを使用する

単一列選択の場合、単一列インデックスの方が多重列インデックスよりも効率的な場合があります。

CREATE INDEX idx_name ON customers (name);

SELECT * FROM customers WHERE name = 'John Doe';

WHERE句で列の等価比較を使用すると、インデックスがより効率的に使用されます。

SELECT * FROM customers WHERE name = 'John Doe';

-- より効率的な書き方
SELECT * FROM customers WHERE name = 'John Doe' AND age IS NULL;

テーブルのサイズが小さいほど、データ検索が速くなります。

不要なデータを削除する。
テーブルを分割する。

キャッシュを使用する

クエリ結果をキャッシュすると、同じクエリを繰り返し実行する際、データ検索速度が向上します。

単一列選択を高速化する方法はいくつかあります。状況に合わせて適切な方法を選択してください。

sqlite indexing