CODEWITHSUNDEEP
sqliteselectwhere-clause
Temüjin
Temüjin

Reputation: 15548

SQLite takes too much time on using OR condition with WHERE clause

I have a SQLite database (table) with 100 million rows.

Table schema:

CREATE TABLE users
(
    u_id      VARCHAR(32) PRIMARY KEY,
    u_status  BOOLEAN,
    u_country VARCHAR(2),
    u_score   INT
);

CREATE INDEX dex_sta ON users (u_status);
CREATE INDEX dex_cou ON users (u_country);
CREATE INDEX dex_sco ON users (u_score);

CREATE INDEX dex_sns ON users (u_status, u_score);
CREATE INDEX dex_mul ON users (u_status, u_country, u_score);

When I use these below simple queries without choosing multiple countries I got response in 15 ms.

SELECT * FROM users WHERE u_status = 1 AND u_country = 'US' ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

SELECT * FROM users WHERE u_status = 1 AND u_country = 'IN' ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

Problem starts from this below query

When I try to match multiple countries with OR condition query takes 60 seconds to respond.

SELECT * FROM users WHERE u_status = 1 AND (u_country = 'US' OR u_country = 'IN') ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

Query Improvement 1:

When I force query to use specific INDEX via (INDEXED BY) it responded in 1 second

SELECT * FROM users INDEXED BY dex_mul WHERE u_status = 1 AND (u_country = 'US' OR u_country = 'IN') ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

Query Improvement 2:

When I'm using UNION ALL query it takes 500 milliseconds to respond

SELECT * FROM users WHERE u_status = 1 AND u_country = 'US'
UNION ALL
SELECT * FROM users WHERE u_status = 1 AND u_country = 'IN'
ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

Is it possible to get response in < 50 ms like first query with matching multiple countries?

Upvotes: 1

Views: 184

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Using the OR condition in the query doesn't allow the use of Indexes. You can convert your query to UNION ALL clause - 

SELECT *
FROM users
WHERE u_status = 1 AND u_country = 'US'
UNION ALL
SELECT *
FROM users
WHERE u_status = 1 AND u_country = 'IN'
ORDER BY u_score DESC LIMIT 10 OFFSET 100000;

This might solve your problem.

Upvotes: 1

Related Questions