Show Syntax¶

MemSQL supports the following SHOW syntax:

SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern']

Shows the list of databases that exist on this MemSQL instance.

SHOW TABLES [{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]

Shows the list of tables in the current selected database, or in another database if db_name is specified.

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]

Shows the list of columns within a given table. LIKE ‘pattern’ can be used to filter the list by name. WHERE expr can be used to filter the results by other predicates.

SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]

Shows the list of indexes associated with a given table.

SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]

Shows the list of variable bindings currently in scope.

SHOW [GLOBAL | SESSION] STATUS
[LIKE 'pattern' | WHERE expr]

Shows a subset of the same status information that MySQL shows via this command.

SHOW TABLE STATUS [{FROM | IN} db_name]

Shows how much memory this table is using for row data. Memory for off-row data (such as VARCHARs) isn’t reported.

SHOW PROCESSLIST

Shows information about threads currently running.

SHOW STATUS [EXTENDED]

Shows MemSQL server status information. Keyword EXTENDED shows MemSQL specific status.

SHOW PLANCACHE

This is a MemSQL extension (it doesn’t exist in MySQL). It shows all query statements that MemSQL has compiled and executed as well as cumulative query execution statistics associated with each plan.

Here is example of SHOW PLANCACHE output:

memsql> show plancache;
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+
| Database | QueryText                             | Commits | Rollbacks | RowCount | ExecutionTime | LogBufferTime | LogFlushTime | RowLockTime |
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+
| perf     | SELECT * FORM scans WHERE id = @      |     101 |         0 |       98 |             5 |          NULL |         NULL |        NULL |
| perf     | INSERT INTO scans VALUES (?, ?, ?, ?) |     120 |         0 |      120 |             9 |             0 |            0 |           0 |
+----------+---------------------------------------+---------+-----------+----------+---------------+---------------+--------------+-------------+

The columns produced by SHOW PLANCACHE are the following:

• Database : Context database selected with “USE db_name” when query was compiled.
• QueryText : Text of query with all numeric and string parameters replaced by tags (depending on parameter and/or query type). ‘@’ is used for integers and ‘^’ for string parameters. ‘?’ are always used in case of INSERT queries.
• Commits : Number of successful executions of the query.
• Rollbacks : Number of unsuccessful executions of the query (i.e. if the query was aborted or it encountered runtime errors).
• RowCount : Cumulative number of rows returned by SELECT query or cumulative number of rows inserted, updated, deleted in case of INSERT, UPDATE and DELETE queries.
• ExecutionTime : Cumulative time (in milliseconds) spent executing the query.
• LogBufferTime : NULL in case of SELECT queries, otherwise cumulative time (in milliseconds) spent waiting to reserve space in transaction buffer for this query. Larger transaction buffer and faster disk can help reducing it.
• LogFlushTime : NULL in case of SELECT queries, otherwise cumulative time (in milliseconds) spent waiting until changes made by this query are flushed to disk. Faster disk can help reducing it.
• RowLockTime : NULL in case of SELECT queries, otherwise cumulative time (in milliseconds) spent waiting to acquire exclusive row locks.

Note: All counters shown by SHOW PLANCACHE are cleared when MemSQL is restarted.