MySQL command-line tip: compare result sets at Xaprb

MySQL command-line tip: compare result sets

Here’s a quick productivity tip: when optimizing queries by rewriting them to different forms that should return the same results, you can verify that you get the same results by taking a checksum of them.

Just set your pager to md5sum:

mysql> pager md5sum -
PAGER set to 'md5sum -'
mysql> select * from test;
a09bc56ac9aa0cbcc659c3d566c2c7e4  -
4096 rows in set (0.00 sec)

Further Reading:

Written by Xaprb

March 25th, 2009 at 8:45 am

Posted in SQL,Tools

Tagged with MySQL

20 Responses to 'MySQL command-line tip: compare result sets'

Subscribe to comments with RSS

Top-10 tip for me! Thanks.

Jeff Cornejo

25 Mar 09 at 8:52 am
Very, very cool!
Thanks

Giuseppe Maxia

25 Mar 09 at 9:02 am
Very cool! I like very much!

Dups

25 Mar 09 at 9:05 am
very handy! bookmarked! :D

imrui

25 Mar 09 at 9:17 am
Great tip!

Anyone point me how to do this on Windows? Also, my MySQL,when running pager, displays erorr in syntax. Do you have to enable the pager option in the cnf at startup?

Jamie

25 Mar 09 at 9:18 am
Jamie, alas Windows can’t do tricks with the pager :-|

Xaprb

25 Mar 09 at 9:34 am
Wonderful! Thanks

Shlomi Noach

25 Mar 09 at 9:57 am
on Windows, you can do CREATE TEMPORARY TABLE xxx SELECT and see the checksum of that table. But I have never tested this approach :)

krteQ

25 Mar 09 at 10:35 am
Good tip, but it’s worth noting that a checksum will assume the results are bit for bit equivalent. Two relationally equivalent results might have different representation as SQL results, compare a UNION b with b UNION a. One way is to alter your query to make sure it does this, but that might defeat the purpose of optimization.

I haven’t tested this with the mysql pager trick, but sort -u | md5sum – will sort and eliminate duplicates on a line by line basis before calculating the checksum. This won’t work predictably if you have embedded newlines in a text field, unfortunately.

Also, if you want to eliminate case discrepancies, tr ‘[:upper:]‘ ‘[:lower:]‘ will convert everything to (unicode) lowercase, so you can add that to the pipeline. (Add it before the sort command, of course.)

Ben Samuel

25 Mar 09 at 11:59 am
Anyone know if there is an SQL Server or Oracle equivalent of this?

bofe

25 Mar 09 at 12:19 pm
I like this trick – i can see it being very useful. Gets me thinking of other nifty things in a similar vein; you could redirect the first results to a file, and then set pager to diff/sdiff – the possiblities are endless!
Alot of the time i find myself running a query, dumping to a file, and then running wc, bc, sed, sort etc on it. From now on, i will simply set my pager to the appropriate command!

I don’t have a client handy – can anyone confirm if |’s work in the pager? Ie, can I set my pager to something like “sort -u | tr -s ‘ ‘ | cut -f 2 -d ‘ ‘”?

GBA

25 Mar 09 at 2:30 pm
bofe:

hackish, untested, and exclusive of text, ntext, & varbinary, but for mssql…

select hashbytes(‘md5′, convert(varchar, (select checksum_agg(binary_checksum(*)) from tblname)))

You can skip hashbytes() if you don’t need md5.

pfzzzt

25 Mar 09 at 2:35 pm
A similar tip: http://datacharmer.blogspot.com/2009/03/another-command-line-tip.html

Giuseppe Maxia

26 Mar 09 at 9:19 am
[...] here Baron is in his xaprb mask with a MySQL command-line tip to compare result sets. Very [...]

Pythian Group - Blog

27 Mar 09 at 1:18 pm
Wow, great tip! This can be really useful from time to time.

Martijn Engler

27 Mar 09 at 1:38 pm
nice tip. btw, I think most Solaris wont ship with md5sum, but you can accomplish the same using digest:

mysql> pager /usr/bin/digest -a md5
PAGER set to ‘/usr/bin/digest -a md5′
mysql> select * from test;
09fb51bb0ba552611dec195c63ca27ea
1 row in set (0.00 sec)

Robert Treat

27 Mar 09 at 2:16 pm
[...] http://www.xaprb.com/blog/2009/03/25/mysql-command-line-tip-compare-result-sets/ : une utilisation astucieuse du pager de MySQL pour comparer des rÃ©sultats [...]

Dev Blog AF83 » Blog Archive » Veille technologique : A la une, MÃ©thodes, Outils, Javascript, PHP, MySQL, Performances, Wallpapers

31 Mar 09 at 11:16 am
[...] A while ago Baron Schwartz published a MySQL command-line tip: compare result sets. [...]

A note on Baron’s command line tip for comparing result sets | code.openark.org

16 Apr 09 at 4:02 am
[...] found a really helpful tip on Xaprb for comparing result sets in MySQL: mysql> pager md5sum – PAGER set to ‘md5sum -’ mysql> [...]

Comparing MySQL result sets quickly | Racker Hacker

5 May 09 at 11:51 am
Just look for mysql pager info on: http://linux-sysadmin.org/2010/08/mysql-pager-filter-your-output/

Derek

2 Sep 10 at 4:13 am