Memurai SQL quick start
For a quick introduction to Memurai SQL and to try out some commands, ensure that you have the Memurai SQL module loaded and ready to run in your CLI. For more information, see Memurai SQL Installation.
Create an index
The MSQL.CREATE
command enables you to create an in-memory index table for a particular group of related entries that share a common prefix.
Let’s say you have several hash entries in Memurai or Valkey for Customers for example, with the key being used having the same prefix ie. Customer:001
, Customer:002
, Customer:003
etc.
127.0.0.1:6379> hset Customer:001 Name "Paul Spellman" Company "Widgets Inc" Contact_Number 123456789
(integer) 3
127.0.0.1:6379> hset Customer:002 Name "Max Jones" Company "XYZ Inc" Contact_Number 912334234
(integer) 3
127.0.0.1:6379> hset Customer:003 Name "Ray Brown" Company "Acme Ltd" Contact_Number 893345834
(integer) 3
In this case, you would specify Customer:
as the prefix to use in the index creation. You then add the rows that the index will contain, and specify the data types for each, such as TEXT
, BIGINT
, FLOAT
, etc.
So, to create an index table for Customers
called customers_idx
, with the column names Name
, Company
, and Contact_Number
, use the following command:
127.0.0.1:6379> MSQL.CREATE customers_idx ON HASH Customer: Name TEXT Company TEXT Contact_Number BIGINT
The output confirms that the customer_idx
table was created, and shows the number of keys that were scanned, and the names of the columns.
127.0.0.1:6379> MSQL.CREATE customers_idx ON HASH Customer: Name TEXT Company TEXT Contact_Number BIGINT
1) "Table created: 'customers_idx'"
2) "scanned 3 keys."
3) "0 NULLs in column Name"
4) "0 NULLs in column Company"
5) "0 NULLs in column Contact_Number"
Search the index
You can search your in-memory index table for a particular string, number, or any combination of the fields in your datastore.
The command to use is MSQL.QUERY
, and you specify the arguments by using SELECT
and FROM
, with other possible combinations much like you would with SQL.
To search your Customers index for the list of names and companies, use the following command:
127.0.0.1:6379> MSQL.QUERY "SELECT Name, Company FROM customers_idx"
It returns the following output:
1) 1) "Name"
2) "Company"
2) 1) "Max Jones"
2) "XYZ Inc"
3) 1) "Paul Spellman"
2) "Widgets Inc"
4) 1) "Ray Brown"
2) "Acme Ltd"
For a complete list of the Memurai SQL commands and possible combinations available with MSQL.QUERY, see Memurai SQL Commands.
Add data to the index
If you have data already in the table from existing hashes such as Customer:001
as shown previously, or you created the index table without any existing data, you can add data in Memurai (or Valkey) and it will be reflected in the index table.
If you add another customer such as the following:
127.0.0.1:6379> hset Customer:004 Name "Robert Johnson" Company "BigCo Inc" Contact_Number 789123456
(integer) 3
The customers_idx
table will be updated with the new entry and is reflected in any subsequent searches or queries that you run on the index. For example, if you run the same query again:
127.0.0.1:6379> MSQL.QUERY "SELECT Name, Company FROM customers_idx"
The following output is returned, with the additional entry displayed at the end:
1) 1) "Name"
2) "Company"
2) 1) "Max Jones"
2) "XYZ Inc"
3) 1) "Paul Spellman"
2) "Widgets Inc"
4) 1) "Ray Brown"
2) "Acme Ltd"
5) 1) "Robert Johnson"
2) "BigCo Inc"
Drop the index
If you want to remove the index completely from your in-memory datastore, you can do so with the following command:
127.0.0.1:6379> MSQL.DESTROY customers_idx
The following output is returned, and the index no longer exists.
1) "msql.destroy: Table customers_idx dropped."