ORIGINAL QUERY 4.5 User
100,000 Users
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;
Search specific index
CREATE INDEX index_user_searchName ON user
(userId, name, createdAt) 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 10 OFFSET 0;
13s
NOTES:
- composite indexes still do not work. It seems that only the first property is being searched. To avoid this we have created separate indexes per property conditional
Individual indexes for each condition
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 name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
1.7s (0 results)
SELECT userId, avatarId, name FROM user
WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 1000 OFFSET 0;
1.7s (0 results)
SELECT userId, avatarId, name FROM user
WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10000 OFFSET 0;
1.7s (0 results)
SELECT userId, avatarId, name FROM user
WHERE name IS NULL AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100 OFFSET 0;
18s (100 results)
SELECT userId, avatarId, name FROM user
WHERE name IS NULL AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 1000 OFFSET 0;
18s (1000 results)
SELECT userId, avatarId, name FROM user
WHERE name IS NULL AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10000 OFFSET 0;
18s (10000 results)
NOTES:
- Significant performance different between checking data type (LIKE % is actually faster than a NULL check)
userId
SELECT userId FROM user
WHERE userId IS NULL AND meta().id LIKE “user/%” LIMIT 100;
9ms (0 results)
SELECT userId FROM user
WHERE userId IS NULL AND meta().id LIKE “user/%” LIMIT 1000;
9ms (0 results)
SELECT userId FROM user
WHERE userId IS NULL AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 100;
9ms (0 results)
SELECT userId FROM user
WHERE userId IS NULL AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 1000;
9ms (0 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” LIMIT 100;
13ms (100 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” LIMIT 1000;
48ms (1000 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” LIMIT 10000;
490ms (10000 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 100;
1.5s (100 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 1000;
1.6s (1000 results)
SELECT userId FROM user
WHERE userId <> “” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 10000;
2.2s (10000 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” LIMIT 100;
45ms (100 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” LIMIT 1000;
250ms (1000 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” LIMIT 10000;
1.9s (10000 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 100;
45ms (100 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 1000;
300ms (1000 results)
SELECT userId, avatarId , name FROM user
WHERE userId LIKE “%” AND meta().id LIKE “user/%” ORDER BY userId ASC LIMIT 10000;
1.8s (10000 results)
NOTES:
- ORDER BY is significantly faster
- Performance impacts change depending on the data type we try and compare (NOT empty string check is slower than % wildcard)
name
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 100;
12ms (100 results)
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 1000;
45ms (1000 results)
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 10000;
450ms (10000 results)
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100;
12ms (100 results)
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 1000;
45ms (1000 results)
SELECT name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10000;
450ms (10000 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 100;
30ms (100 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 1000;
180ms (1000 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” LIMIT 10000;
1.5s (10000 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100;
30ms (100 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 1000;
180ms (1000 results)
SELECT userId, avatarId , name FROM user
WHERE name IS NULL AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10000;
1.5s (10000 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE “user/%” LIMIT 100;
100ms (0 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE "user/%"LIMIT 1000;
100ms (0 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE “user/%” LIMIT 10000;
100ms (0 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 100;
100ms (0 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 1000;
100ms (0 results)
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10000;
100ms (0 results)
NOTES:
- As expected there was no performance impact with ORDER BY when there is nothing to order, e.g. values are NULL
- Still have performance impacts when comparison data type is changed
name + userId
SELECT userId, avatarId , name FROM user
WHERE name LIKE “%” AND userId <> “” AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10;
1.6s
name + userId + createdAt
SELECT userId, avatarId, name FROM user
WHERE name IS NULL AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10;
18s
SELECT userId, avatarId, name FROM user
WHERE name IS NULL AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10 OFFSET 0;
18s
SELECT userId, avatarId, name FROM user
WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10;
1.7s
SELECT userId, avatarId, name FROM user
WHERE name LIKE “%” AND userId <> “” AND createdAt <= 99999999999999999 AND meta().id LIKE “user/%” ORDER BY name ASC LIMIT 10 OFFSET 0;
1.4s