Use EXPLAIN to view the execution plan of a SQL statement without running it. This is useful when optimizing queries — for example, to check whether an index is being used effectively.
Applicable engines and versions
EXPLAIN is supported by LindormTable and LindormTSDB.
Plan depth options (
WITH TYPEandWITHOUT IMPLEMENTATION) require LindormTable 2.6.3 or later and Lindorm SQL 2.6.4 or later. To check your Lindorm SQL version, see SQL versions.
Syntax
explain_statement ::= EXPLAIN [ PLAN ] [ explain_depth_expression ]
[ FOR ] select_statement;
explain_depth_expression ::= { WITH TYPE | WITHOUT IMPLEMENTATION }Parameters
Plan depth
The execution plan returned by EXPLAIN has three depth levels. Use the optional explain_depth_expression to select the depth.
| Depth | Expression | When to use |
|---|---|---|
| Physical plan | (none) | General query optimization. Returns the fully optimized plan after the optimizer applies filtering. |
| Logical plan | WITHOUT IMPLEMENTATION | Debugging query structure before optimization. Returns the unoptimized logical plan. |
| Type plan | WITH TYPE | Quickly inspecting result set column types without seeing the full plan. Returns only the types of each column in the result set. |
SELECT statement
The select_statement is the query whose execution plan you want to view.
Examples
The following examples use this table:
CREATE TABLE test (
p1 VARCHAR(255) NOT NULL,
c1 VARCHAR(255),
PRIMARY KEY (p1)
);With this data:
INSERT INTO test (p1, c1) VALUES
('3abc9378', '10'),
('3def9378', NULL),
('3ghi1234', '15'),
('3jkl5678', NULL),
('3mno9378', '20'),
('3pqr0000', '25'),
('3stu9378', '30'),
('3vwx0000', NULL),
('3yz_9378', '35'),
('3_1239378', '40');All three examples run EXPLAIN against the same query, each at a different depth.
View the physical plan
Returns the optimized execution plan. Use this for most query optimization tasks.
EXPLAIN SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;View the logical plan
Returns the unoptimized logical plan. Use this when you need to inspect the raw query structure before the optimizer rewrites it.
EXPLAIN WITHOUT IMPLEMENTATION FOR SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;View the type plan
Returns only the types of the result set columns. Use this for a quick type check without loading the full plan.
EXPLAIN WITH TYPE FOR SELECT POSITION('9378' IN p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;