I have a field that stores JSON data. But I only need the field to be returned in queries (eg: select json_field…). I never need Manticore to read the JSON in there.
What I want to know is the most disk-efficient (ie: compressed) and memory-efficient (ie: stored on disk only) way of doing this.
It sounds like that might be to enable compression, then define it as a string/text field, then make sure it’s stored only. This would mean that each time the field is returned, it has to read from disk. I am running on NVME drives and am fine with that.
The other thing I have considered is storing it as a JSON field/attribute type. But the documentation says that is stored rowwise, which I believe is in memory. Is there a way to store it as a JSON field/attribute type, but have it stored only on disk?
Is my understanding of this correct?
If so, is this how a table creation statement would look for this kind of field? (all my other fields/attributes/settings have been removed)
CREATE TABLE IF NOT EXISTS test_table (
doc_json text stored
)
docstore_block_size = '4k'
docstore_compression = 'lz4hc'
docstore_compression_level = '9'
Attributes load into memory on index load or during the request processing that is why if you do not need any operations on that JSON it best to keep it stored.
I just tried crreating my main table using stored_only_fields by using a CREATE TABLE mysql statement. It failed on using stored_only_fields. To test it, I reduced everything to this and am still getting the error:
CREATE TABLE IF NOT EXISTS test (
id bigint,
json__doc__txt_s text stored
)
stored_only_fields = 'json__doc__txt_s'
seems create table t (s1 text stored) already creates stored only field
in the example below f indexed and stored but s1 only stored
mysql> create table t (f text, s1 text stored) stored_only='s1';
Query OK, 0 rows affected (0.01 sec)
mysql> desc t;
+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| f | text | indexed stored |
| s1 | text | stored |
+-------+--------+----------------+
3 rows in set (0.00 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t (f text, s1 text stored);
Query OK, 0 rows affected (0.02 sec)
mysql> desc t;
+-------+--------+----------------+
| Field | Type | Properties |
+-------+--------+----------------+
| id | bigint | |
| f | text | indexed stored |
| s1 | text | stored |
+-------+--------+----------------+
3 rows in set (0.00 sec)
and match finds nothing from such stored only fields
mysql> insert into t (f,s1) values ('t1', 't2'), ('t2','t3');
Query OK, 2 rows affected (0.01 sec)
mysql> select * from t where match ('t2');
+---------------+------+------+
| id | f | s1 |
+---------------+------+------+
| 1677721600002 | t2 | t3 |
+---------------+------+------+
1 row in set (0.00 sec)
--- 1 out of 1 results in 0ms ---
mysql> select * from t where match ('t3');
Empty set (0.00 sec)
--- 0 out of 0 results in 0ms ---
Okay, that makes sense. I had originally assumed that specifying it only as stored would only store it. but i didn’t know if stored_only was also required.
I’m also seeing my mistake now that i was using ‘stored_only_fields’, but you’re using the ‘stored_only’ as the option for the create table statement.
I didn’t see stored_only in the documentation, and it might help others if that and that stored definitly only stores it on disk and not in memory. Are you guys open to me submitting a pull request with a quick note on this? It looks like it would be on this page: