Xaprb

Stay curious!

MySQL command-line tip: compare result sets

with 20 comments

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)

Written by Xaprb

March 25th, 2009 at 8:45 am

Posted in SQL,Tools

Tagged with

«
»

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

Subscribe to comments with RSS

  1. Top-10 tip for me! Thanks.

    Jeff Cornejo

    25 Mar 09 at 8:52 am

  2. Very, very cool!
    Thanks

    Giuseppe Maxia

    25 Mar 09 at 9:02 am

  3. Very cool! I like very much!

    Dups

    25 Mar 09 at 9:05 am

  4. very handy! bookmarked! :D

    imrui

    25 Mar 09 at 9:17 am

  5. 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

  6. Jamie, alas Windows can’t do tricks with the pager :-|

    Xaprb

    25 Mar 09 at 9:34 am

  7. Wonderful! Thanks

    Shlomi Noach

    25 Mar 09 at 9:57 am

  8. 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

  9. 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

  10. Anyone know if there is an SQL Server or Oracle equivalent of this?

    bofe

    25 Mar 09 at 12:19 pm

  11. 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

  12. 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

  13. A similar tip: http://datacharmer.blogspot.com/2009/03/another-command-line-tip.html

    Giuseppe Maxia

    26 Mar 09 at 9:19 am

  14. [...] 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

  15. Wow, great tip! This can be really useful from time to time.

    Martijn Engler

    27 Mar 09 at 1:38 pm

  16. 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

  17. [...] 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

  18. [...] 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

  19. [...] 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

  20. 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

Leave a Reply