How can I copy the MySQL EXPLAIN result into a table?

Question

I'd like to archive the results of a series of query EXPLAIN plans into a table for later analysis.

I created a table that contains all the fields from an EXPLAIN result, but I can't figure out how to populate it from executing an EXPLAIN command.

I figured out how to export a .sql file in MySQL Workbench that contains INSERT commands, but I have to edit this manually to make it work. Is the EXPLAIN information stored somewhere so I could write queries to do it automatically? I can't find it in either the information_schema or performance_schema.

• oNare: Did you find a way to achieve this?
• kcd: No. I wanted to do it in MySQL Workbench and the only way I could do it was to export the tabular EXPLAIN results as a .sql file (a sequence of insert commands) that I could then modify and execute to save the query plan in my own benchmark database.
• oNare: Did yo try any answer or are you going to post yours? Let us know.
• kcd: My answer is that I used the Export button in MySQL Workbench (v. 6.3) after running the EXPLAIN command with a query. You can toggle back and forth between the tabular and the visual query plan. I exported the tabular view (one line per table in the query) to a .sql file. This generates a file with one INSERT command per line in the EXPLAIN output. You can then modify the file and INSERT directly into your own table that mimics the columns in the tabular EXPLAIN. That's how I save the results. It's not pretty but it works.
• kcd: You can also save the visual EXPLAIN plan as a .png file.

Answer 1

If you are running on Linux OS you could save it into a file.

Example:

User='root'
Pass='text'
Host='10.0.0.223' (If it's remote)

On command line run:

mysql -u $User -p$Pass --host=$Host -e "EXPLAIN SELECT * FROM mysql.user LIMIT 0,2" | tee -a test.txt

mysql -e will execute the query on the command line.

tee -a will read from standard input and write to file test.txt

And you'll get:

enter image description here

You can use perl to replace the death lines to any delimiter you want (I used comma in this example).

perl -wnlpi -e 's/\s+/,/g;' text.txt

Example:

enter image description here

Hope this help.

Answer 2

create a table with same number of columns & same column name(for future reference) & then execute these:

"INSERT INTO my_audit_tbl EXPLAIN SELECT * FROM tablename;"

• ypercubeᵀᴹ: INSERT ... EXPLAIN SELECT ... is not valid syntax.