Examine large SQL queries - Revision history

Smsldoo at 14:25, 25 March 2014

2014-03-25T14:25:10Z

Smsldoo: /* Export results for query */

2012-03-17T16:35:54Z

‎Export results for query

Smsldoo at 20:41, 17 February 2012

2012-02-17T20:41:49Z

Smsldoo at 17:40, 17 February 2012

2012-02-17T17:40:43Z

Smsldoo: /* Sort records returned by characters */

2012-02-17T17:35:15Z

‎Sort records returned by characters

Smsldoo: Created page with "==Overview== 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 ..."

2012-02-17T17:32:38Z

Created page with "==Overview== 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 ..."

New page

==Overview==
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.
<code>[bash,n]
mysql -e "show full processlist;"
</code>

==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.
<code>[bash,n]
mysql $database -e "examine $query;"
</code>

==Export results for query==

To be able to look at the query easier you will want to export the query.
<code>[bash,n]
mysql $databse -e "$query;" > sqlquery.sql
</code>

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.
<code>[bash,n]
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
</code>

==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.
<code>[bash,n]
grep "$record" sqlquery.sql > record.sql
</code>

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

← Older revision		Revision as of 20:41, 17 February 2012
Line 1:		Line 1:
	[[Category:One Liners]]		[[Category:One Liners]]
−	~~==Overview==~~
	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.		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.

← Older revision		Revision as of 17:40, 17 February 2012
Line 1:		Line 1:
		+	[[Category:One Liners]]
	==Overview==		==Overview==
	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.		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.

@@ Line 7: / Line 7: @@
 The full query is important, so lets go ahead and get the full thing.
-<code>[bash,n]
 mysql -e "show full processlist;"
-</code>
+</source>
 ==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.
-<code>[bash,n]
 mysql $database -e "examine $query;"
-</code>
+</source>
 ==Export results for query==
 To be able to look at the query easier you will want to export the query.
-<code>[bash,n]
 mysql $datbase -e "$query;" > sqlquery.sql
-</code>
+</source>
 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.
@@ Line 33: / Line 36: @@
 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.
-<code>[bash,n]
 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
-</code>
+</source>
 ==Export the top record or records==
@@ Line 41: / Line 45: @@
 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.
-<code>[bash,n]
 grep "$record" sqlquery.sql > record.sql
-</code>
+</source>
 Obviously at this point you can once again to ls -lah record.sql to get the actual size.

@@ Line 31: / Line 31: @@
 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.
 <code>[bash,n]
 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