SELECT sql performance improvement

General Discussions - English
1

I have RT table with text, string, json, bigint & ‘mva64’ columns in myTable. The text columns are indexed stored and table will have more than 10 millions of data. when we using the select sql with where clause of string & json and order by clause for bigint column. The below type of query taking more time. The requirement is should be some msec. How can we tune this by SQL or database level.

CREATE TABLE myTable (
id bigint,
summary text,
searchable text,
rich_text text,
notes text,
object_id string attribute,
cust_id string attribute,
updatedat bigint,
createdat bigint,
raw_data json,
queues multi64,
actors multi64,
status multi64,
watchers multi64
);

SELECT count(*) as total FROM myTable WHERE cust_id='24ac14df-8e64-4634-94c6-7fb83f450ff9' AND data.deleted_seq=-1;

SELECT object_id, raw_data, itemtypeid FROM case_values_v2_test_revision_2 WHERE cust_id='24ac14df-8e64-4634-94c6-7fb83f450ff9' AND data.deleted_seq=-1 ORDER BY updatedat desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;

SELECT count(*) as total FROM myTable WHERE ANY(actors)=6565760154226615 AND cust_id='24ac14df-8e64-4634-94c6-7fb83f450ff9' AND data.deleted_seq=-1;

SELECT object_id, raw_data, itemtypeid FROM case_values_v2_test_revision_2 WHERE ANY(actors)=6565760154226615 AND cust_id='24ac14df-8e64-4634-94c6-7fb83f450ff9' AND data.deleted_seq=-1 ORDER BY updatedat desc LIMIT 1000 OPTION retry_count=0, retry_delay=0;
2

In general manticore doesn’t use an ‘index’ for attribute queries. Its a ‘full-table scan’. Table scans are generally quite quick because the entire attribute dataset should be in RAM.

Might want to consider ‘columnar’ storage
https://manual.manticoresearch.com/Creating_a_table/Data_types#Row-wise-and-columnar-attribute-storages
…which be used to create an ‘index’ on attribute data. Dont have much experience, but it might work for you.

Your queries could perhaps be optimized by using fields (ie marked as ‘text’ rather than attribute), for the and MATCH query. Its intended for word searching, but could could for example, search for your cust_id within text fields. But will need more work (eg making sure charset_table supports it)

1 Like