Use case: IM application with the Lindorm wide table and search engine

Table schema design

This guide uses a table named im_messages to store message data.

Create table statement

Run the following SQL statement to create the im_messages table:

CREATE TABLE IF NOT EXISTS im_messages (
    msg_id VARCHAR,
    from_user VARCHAR,
    to_user VARCHAR,
    group_id VARCHAR,
    content VARCHAR,
    msg_type VARCHAR,
    send_time BIGINT,
    status VARCHAR,
    is_deleted BOOLEAN,  -- We recommend adding a soft delete flag for recalling messages.
    PRIMARY KEY(msg_id)
);

Write test data

Use the UPSERT statement to insert or update data. The following example shows how to insert five sample messages:

-- Insert five test messages
UPSERT INTO im_messages (
    msg_id,
    from_user,
    to_user,
    group_id,
    content,
    msg_type,
    send_time,
    status
) VALUES
('m001', 'u1001', 'u1002', NULL, 'Hello!', 'text', 1704000000000, 'read'),
('m002', 'u1002', 'u1001', NULL, 'Hi there', 'text', 1704000060000, 'read'),
('m003', 'u1001', NULL, 'g2001', 'Hello everyone', 'text', 1704000120000, 'sent'),
('m004', 'u1003', NULL, 'g2001', 'Got it', 'text', 1704000180000, 'delivered'),
('m005', 'u1001', 'u1004', NULL, 'image.jpg', 'image', 1704000240000, 'sent');

Query data

• Primary key query (most efficient)

  Query the content of a specific message based on its ID. This is the most efficient point query method in Lindorm.

  SELECT content 
  FROM im_messages 
  WHERE msg_id = 'm001';

• Simple aggregate analysis

  Note: Without an index, the following aggregate queries trigger a full table scan. You can use them to verify logic on small datasets, but in a production environment, you must use a search index or columnar storage for efficient execution.

  • Count the total number of messages in the table

    SELECT COUNT(*) FROM im_messages;

  • Count the number of messages by message type

    SELECT msg_type, COUNT(*) FROM im_messages GROUP BY msg_type;

  • Check if a specific user has unread messages

    SELECT COUNT(*) FROM im_messages WHERE to_user = 'u1002' AND status = 'sent';

Index design

The following indexes are designed for typical IM query scenarios:

Create index statements

Run the following SQL statements to create the secondary indexes. The creation process can take from seconds to several minutes, depending on the amount of existing data.

-- 1. [Key] Sender + time (descending) - for "querying a user's most recent messages"
CREATE INDEX IF NOT EXISTS idx_from_user_time ON im_messages (from_user, send_time DESC);

-- 2. [Key] Receiver + time (descending) - for "querying a user's most recent direct messages"
CREATE INDEX IF NOT EXISTS idx_to_user_time ON im_messages (to_user, send_time DESC);

-- 3. [Key] Group + time (descending) - for "querying a group's latest messages"
CREATE INDEX IF NOT EXISTS idx_group_time ON im_messages (group_id, send_time DESC);

-- 4. Time index - less common alone, typically used in combination with other conditions
CREATE INDEX IF NOT EXISTS idx_send_time ON im_messages (send_time);

Query examples using a secondary index

• Query the 10 most recent messages sent by user u1001 (hits the idx_from_user_time index)

  SELECT msg_id, content, send_time
  FROM im_messages
  WHERE from_user = 'u1001'
  ORDER BY send_time DESC
  LIMIT 10;

• Query the 5 most recent direct messages received by user u1002 (hits the idx_to_user_time index)

  SELECT msg_id, from_user, content
  FROM im_messages
  WHERE to_user = 'u1002'
  ORDER BY send_time DESC
  LIMIT 5;

• Query the 20 most recent messages in group g2001 (hits the idx_group_time index)

  SELECT msg_id, from_user, content, send_time
  FROM im_messages
  WHERE group_id = 'g2001'
  ORDER BY send_time DESC
  LIMIT 20;

• Query messages sent by u1001 within a specific time range (hits the idx_from_user_time index)

  SELECT msg_id, content
  FROM im_messages
  WHERE from_user = 'u1001'
    AND send_time BETWEEN 1704000000000 AND 1704000300000
  ORDER BY send_time DESC;

Create a search index

Run the following SQL statement to create a search index named im_search_idx. Lindorm automatically synchronizes data from the wide table to the search engine by using the LTS engine.

-- Create a search index named im_search_idx
CREATE INDEX im_search_idx 
USING SEARCH
ON im_messages (
    msg_id,          					-- Exact match (primary key)
    from_user,      					-- Sender (exact match)
    to_user,         					-- Receiver (exact match)
    group_id,        					-- Group ID (exact match)
    content(type=text,analyzer=ik), 	-- Full-text search field (tokenization enabled)
    msg_type,        					-- Message type (exact match)
    send_time,       					-- Timestamp (supports range queries and sorting)
    status           					-- Status (exact match)
);

You can run SHOW INDEX FROM im_messages; to check the index creation status.

Query examples using a search index

• Search for messages that contain "image" (full-text search)

  SELECT msg_id, from_user, content 
  FROM im_messages
  WHERE content LIKE '%image%'

• Query for messages sent by user u1001 that contain "Hello" (multi-condition query)

  SELECT msg_id, content, send_time
  FROM im_messages
  WHERE from_user = 'u1001' AND content LIKE '%Hello%'

• Query the 10 most recent messages in group g2001

  SELECT msg_id, content
  FROM im_messages
  WHERE group_id = 'g2001' 
    AND send_time IS NOT NULL
  ORDER BY send_time DESC
  LIMIT 10;