Information Schema

MemSQL supports the same information_schema views as MySQL does. In addition, the information available in SHOW PLANCACHE, SHOW LEAVES , SHOW AGGREGATORS, and SHOW PARTITIONS is exposed in the information_schema views PLANCACHE, LEAVES, AGGREGATORS and DISTRIBUTED_PARTITIONS.

For example, to find the five most expensive queries, run:

SELECT EXECUTION_TIME/COMMITS, QUERY_TEXT from information_schema.PLANCACHE 
ORDER BY EXECUTION_TIME/COMMITS DESC LIMIT 5

In MemSQL, the PROCESSLIST view also has a PLAN_ID column that can be used to lookup the query currently running on a given connection in PLANCACHE. For example:

SELECT QUERY_TEXT FROM information_schema.PROCESSLIST 
as pl JOIN information_schema.PLANCACHE 
as pc ON pl.PLAN_ID = pc.PLAN_ID

This is the most reliable way to find the query executing on a given connection. The PROCESSLIST.INFO column is not always set by MemSQL.

Was this Article Helpful?

Thanks For Your Feedback!