[query] Ensure that exact-match fulltext attribute checks are efficient #129

New issue

Open

opened 2020-08-06 16:55:24 +00:00 by gburd · 0 comments

gburd commented

2020-08-06 16:55:24 +00:00

(Migrated from github.com)

In a query like this:

[:find ?x [?x :attr/fulltext "foo"]]

we can turn that into a table walk:

SELECT fulltext_datoms123.e AS x
FROM fulltext_datoms fulltext_datoms123
WHERE fulltext_datoms123.a = 456    -- :attr/fulltext
  AND fulltext_datoms123.v = 'foo';

or another table walk:

SELECT datoms0.e AS x
FROM datoms datoms0, fulltext_values fulltext_values1
WHERE fulltext_values1.text = 'foo'
  AND datoms0.v = fulltext_values1.rowid
  AND datoms0.a = 456;

or a match:

SELECT datoms0.e AS x
FROM datoms datoms0, fulltext_values fulltext_values1
WHERE fulltext_values1.text MATCH 'foo' AND fulltext_values1.text = 'foo'
  AND datoms0.v = fulltext_values1.rowid
  AND datoms0.a = 456;

One imagines that the MATCH will be fastest, but it's worth checking.

In a query like this: ``` [:find ?x [?x :attr/fulltext "foo"]] ``` we can turn that into a table walk: ```sql SELECT fulltext_datoms123.e AS x FROM fulltext_datoms fulltext_datoms123 WHERE fulltext_datoms123.a = 456 -- :attr/fulltext AND fulltext_datoms123.v = 'foo'; ``` or another table walk: ```sql SELECT datoms0.e AS x FROM datoms datoms0, fulltext_values fulltext_values1 WHERE fulltext_values1.text = 'foo' AND datoms0.v = fulltext_values1.rowid AND datoms0.a = 456; ``` or a match: ```sql SELECT datoms0.e AS x FROM datoms datoms0, fulltext_values fulltext_values1 WHERE fulltext_values1.text MATCH 'foo' AND fulltext_values1.text = 'foo' AND datoms0.v = fulltext_values1.rowid AND datoms0.a = 456; ``` One imagines that the `MATCH` will be fastest, but it's worth checking.