6

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.

7
  • Did you find a way to achieve this?
    – oNare
    Aug 15, 2015 at 19:51
  • 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.
    – kcd
    Aug 16, 2015 at 21:34
  • Did yo try any answer or are you going to post yours? Let us know.
    – oNare
    Aug 16, 2015 at 22:23
  • 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
    Aug 17, 2015 at 23:53
  • You can also save the visual EXPLAIN plan as a .png file.
    – kcd
    Aug 17, 2015 at 23:53

2 Answers 2

3

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.

-4

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;"

1
  • INSERT ... EXPLAIN SELECT ... is not valid syntax. May 25, 2016 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.