Simple user search takes 25+ seconds to execute on large cluster with fairly small data set

SQL++
2

Here’s more data as I was trying to optimise:

ORIGINAL QUERY
CREATE INDEX index_user ON user(userId , name, consoleUserId) WHERE meta().id LIKE “user/%”;

SELECT userId, avatarId, name FROM user WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
28s

Minimum Condition Query
SELECT userId, avatarId, name FROM user WHERE userId <> “” AND meta().id LIKE “user/%” LIMIT 100;
45ms

With OFFSET
SELECT userId, avatarId, name FROM user WHERE userId <> “” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
43ms

With name condition (name indexed)
SELECT userId, avatarId, name FROM user WHERE userId <> “” AND name LIKE “%” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
28s

With createdAt condition (createdAt not indexed)
SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
26s

With ORDER BY
SELECT userId, avatarId, name FROM user WHERE userId <> “” AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
27s

Create separate name INDEX
CREATE INDEX index_user_name ON user(name) WHERE meta().id LIKE “user/%”;

SELECT userId, avatarId, name FROM user WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
37ms

SELECT userId, avatarId, name FROM user WHERE name IS NULL AND userId <> “” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
122ms

SELECT userId, avatarId, name FROM user WHERE name LIKE “%” AND userId <> “” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
5s

SELECT userId, avatarId, name FROM user WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
28s

Create separate createdAt INDEX
CREATE INDEX index_user_createdAt ON user(createdAt) WHERE meta().id LIKE “user/%”;

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
422ms

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND name LIKE “%” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
1.3s

Make all properties separate indexes
CREATE INDEX index_user_userId ON user(userId) WHERE meta().id LIKE “user/%”;
CREATE INDEX index_user_name ON user(name) WHERE meta().id LIKE “user/%”;
CREATE INDEX index_user_createdAt ON user(createdAt) WHERE meta().id LIKE “user/%”;
CREATE INDEX index_user_consoleUserId ON user(consoleUserId) WHERE meta().id LIKE “user/%”;

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND name IS NULL AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
692ms

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND name LIKE “%” AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
1.5s

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND createdAt <= 99999999999999999 AND name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
28s

Create specific composite index for this query
DROP INDEX user.index_user_userId;
DROP INDEX user.index_user_name;
DROP INDEX user.index_user_createdAt;
CREATE INDEX index_user_searchName ON user(userId, name, createdAt) WHERE meta().id LIKE “user/%”;

SELECT userId, avatarId, name FROM user WHERE userId <> “” AND name LIKE “%” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” LIMIT 100 OFFSET 0;
28s