Examine large SQL queries

If you are running in to problems with specific SQL queries seeming to take a long time to send (ie "Writing to net") you can examine the query to get an idea as to what is happening.

One thing to note, this process was set up for the wp_options table, it assumes that the first row in the field is your key value, if this is not the case you can change it around as needed.

Get the full query

The full query is important, so lets go ahead and get the full thing.

mysql -e "show full processlist;"

Examine the query to see how many records it is accessing

Often times slow downs happen outside of the actual sending due to the number of records being accessed and returned.

mysql $database -e "examine $query;"

Export results for query

To be able to look at the query easier you will want to export the query.

mysql $datbase -e "$query;" > sqlquery.sql

Obviously at this point you can look at the size using ls -lah sqlquery.sql and tell how much data is actually being sent over the network.

Sort records returned by characters

Once you know that the query itself is large you can sort the records returned by the number of characters in the record. Note that the head is optional, but if you have hundreds of records being returned you really only want the top ones.

This step is the one that is dependant on what row is your key value. Generally this is the first row, if not then you may need to tweak.

for i in $(cat sqlquery.sql); do row=`echo "$i"|awk '{print $1}'`; cnt=`echo "$i" | wc -c`; echo $cnt ":" $row;done | sort -nr | head

Export the top record or records

This step is optional, at this point you should have an idea as to the cause of the large query, but if you want to supply the record you can. If you export the record you can get the actual size of that record.

grep "$record" sqlquery.sql > record.sql

Obviously at this point you can once again to ls -lah record.sql to get the actual size.