Convert the MySQL explain plan to string

Question

I need to convert this

+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | prueba2 | ALL  | NULL          | NULL | NULL    | NULL | 9791 |       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

to a string into the mysql DBM to read just this string in an external system.

I need as least this part as a string

|  1 | SIMPLE      | prueba2 | ALL  | NULL          | NULL | NULL    | NULL | 9791 |       |

Step 1: Select the row. Step 2: Copy. Step 3: Paste into an insert statement. — lc., Jul 10 '12 at 17:59
Are you trying to do this programmatically? Is there any reason you can't mysql_dump? — corsiKa, Jul 10 '12 at 18:01
I think you'll find that an EXPLAIN statement returns a result set just like any other statement that returns a result set. It's up to the client to format the results. — Marcus Adams, Jul 10 '12 at 18:19
I wanna get a string like the one returned by PostgreSQL's EXPLAIN clause. — EPadronU, Jul 10 '12 at 18:46

score 0 · Answer 1 · answered Jul 11 '12 at 19:28

0

You can export the results to a file via the command line, but you'll need to parse it and format it exactly as you want:

mysql -e "EXPLAIN SELECT * FROM tbl" -D db -h host -u usr -p pwd > myfile.txt

answered Jul 11 '12 at 19:28

Marcus Adams

53,009
9
91
143

Thank you. I was thinking about something like this `string(EXPLAIN SELECT * FROM tbl)`, but I guess it's not possible. – EPadronU Jul 12 '12 at 04:35

Convert the MySQL explain plan to string

1 Answers1