SQLite で LIKE クエリとインデックスを効果的に活用する方法

SQLiteにおけるLIKEクエリとインデックス使用に関する解説

LIKE句とインデックスの動作

• しかし、以下のパターンでは、インデックスが利用されず、全件検索に近い処理が必要となります。

  • ワイルドカード「*」を含むパターン
  • LIKE句の右側が定数以外の式を含むパターン

インデックス非利用による影響

• インデックスが利用されない場合、LIKE句によるクエリ処理は著しく遅くなります。
• 特に、データ量が多いテーブルの場合、パフォーマンス低下が顕著になります。
• 検索結果の応答時間が長くなり、アプリケーション全体の動作にも影響が出ます。

以下の方法で、インデックス非利用によるパフォーマンス低下を回避できます。

パターンを修正する

• ワイルドカード「*」や「%」をパターン末尾に配置する。
• LIKE句の右側を定数式に置き換える。

例:

-- インデックスが利用されないパターン
SELECT * FROM mytable WHERE name LIKE '%Taro%';

-- インデックスが利用されるパターン
SELECT * FROM mytable WHERE name LIKE 'Taro%';

部分一致ではなく完全一致を使用する

完全一致検索であれば、インデックスが常に利用されます。

-- インデックスが利用されないパターン
SELECT * FROM mytable WHERE name LIKE '%Taro%';

-- インデックスが利用されるパターン
SELECT * FROM mytable WHERE name = 'Taro';

FTS (Full Text Search)モジュールを使用する

FTSモジュールは、全文検索機能を提供しており、LIKE句よりも高速な検索処理が可能です。

クエリを工夫する

• 複数のLIKE句を組み合わせて使用する。
• WHERE句に他の条件を追加する。

-- インデックスが利用されないパターン
SELECT * FROM mytable WHERE name LIKE '%Taro%' AND age >= 20;

-- インデックスが利用されるパターン
SELECT * FROM mytable WHERE name LIKE 'Taro%' AND age >= 20 AND city = 'Tokyo';

データ量が多い場合は、テーブルの設計を検討する

• 頻繁にLIKE句で検索される列を別途のテーブルに分割する。
• 検索条件に適した列でインデックスを作成する。

SQLiteにおけるLIKE句とインデックス使用に関する基本的な知識と、インデックス非利用による影響と回避策について解説しました。これらの情報を参考に、適切なクエリ設計とデータ設計を行い、アプリケーションのパフォーマンス向上を目指してください。

Example 1: Creating an index on a column and using it with a LIKE query

CREATE TABLE customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE INDEX idx_customers_name ON customers (name);

SELECT * FROM customers WHERE name LIKE 'Jo%';

In this example, an index is created on the name column of the customers table. This index can then be used to efficiently search for customers whose names start with the letter "J".

Example 2: Using a wildcard at the end of a pattern

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

CREATE INDEX idx_products_name ON products (name);

SELECT * FROM products WHERE name LIKE '%t-shirt';

In this example, the wildcard * is placed at the end of the pattern %t-shirt. This allows the index to be used to search for products whose names end with the word "t-shirt".

Example 3: Using a partial match with an index

CREATE TABLE employees (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  department TEXT NOT NULL
);

CREATE INDEX idx_employees_department ON employees (department);

SELECT * FROM employees WHERE department LIKE '%Sales%';

Example 4: Avoiding index usage by placing the wildcard at the beginning of the pattern

CREATE TABLE cities (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  country TEXT NOT NULL
);

CREATE INDEX idx_cities_country ON cities (country);

SELECT * FROM cities WHERE country LIKE '%Japan';

In this example, the wildcard % is placed at the beginning of the pattern %Japan. This prevents the index from being used, and the query will perform a full table scan.

Example 5: Using a full table scan for a complete wildcard search

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author TEXT NOT NULL
);

CREATE INDEX idx_books_title ON books (title);

SELECT * FROM books WHERE title LIKE '%';

In this example, the wildcard % is used by itself. This is equivalent to searching for all books, and the index will not be used. The query will perform a full table scan.

These examples illustrate the different ways in which indexes can be used with LIKE queries in SQLite. By understanding how indexes work, you can write more efficient queries that improve the performance of your applications.

Here is an example of how to use the FTS module to search for customers whose names start with the letter "J":

CREATE TABLE customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

CREATE FTS5 VIRTUAL TABLE customers_fts5 (
  id INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO customers_fts5 (id, name) SELECT id, name FROM customers;

SELECT * FROM customers_fts5 WHERE name MATCH 'Jo%';

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL NOT NULL
);

SELECT * FROM products WHERE name REGEXP '.*t-shirt.*';

I hope this helps!