Hey Gerald,
Thanks for bearing with me here. I’ve ran a few additional tests to try to work through the index expression orders. The results are below. Starting with no indexes, and then adding / removing indexes.
As you can see, with no indexes, all the data is pulled fine. There should be a total of 37 items with _type = board and tId = ‘team::2’; In addition, there should be a total of 93 items where tId = 93;
I then go on to create a few indexes, starting with only one expression, and then two expressions, in different orders. In both cases, the multiple expression indexes are not as I’d expect, and based on your response earlier, not as you’d expect either.
Let me know what you think.
Thanks,
Chris
————————————————————————————————————————
NO INDEX
————————————————————————————————————————
SELECT * FROM default WHEre _type = ‘board’;
(RESULT - 37 count)
SELECT * FROM default WHERE _type = ‘board’ AND tId = ‘team::2’
(RESULT - 37 count)
SELECT * FROM default WHERE tId = ‘team::2’
(Result - 93 count)
————————————————————————————————————————
CREATE INDEX test ON default (_type);
————————————————————————————————————————
SELECT * FROM default WHERE _type = ‘board’;
(Result - 37 count)
SELECT * FROM default WHERE _type = ‘board’ AND tId = ‘team::2’
(Result - 37 count)
DROP INDEX default.test;
————————————————————————————————————————
CREATE INDEX test ON default (_type, tId);
————————————————————————————————————————
SELECT * FROM default WHERE _type = ‘board’;
(Result - 0 count)
SELECT * FROM default WHERE _type = ‘board’ AND tId = ‘team::2’
(Result - 0 count)
SELECT * from default where tId = ‘team::2’;
(Result - 93 count)
DROP INDEX default.test;
————————————————————————————————————————
CREATE INDEX test ON default (tId, _type);
————————————————————————————————————————
SELECT * FROM default WHERE _type = ‘board’;
(Result - 37 count)
SELECT * FROM default WHERE _type = ‘board’ AND tId = ‘team::2’
(Result - 0 count)
SELECT * from default where tId = ‘team::2’;
(Result - 0 count)
DROP INDEX default.test;