Python script to count occurrences of a field value in MySQL

mysql-count-value.py

#!/usr/bin/env python
#
# Using MySQL's grouping and counting features for very large tables is
# very slow. This Python script makes it much faster to count unique
# values of a given column. It selects the column values in reasonably-sized
# chunks (10k rows), and feeds those values into a collections.Counter.
#
# Assumptions: you have a .my.cnf set up to connect to the right DB with the
# right username and password. Your table has an id field, which is numeric and
# ideally autoincrement (for smooth distribution of values).
import subprocess
import collections
import threading

process = subprocess.Popen("mysql -B --skip-column-names", shell=True, stdin=subprocess.PIPE, stdout=subprocess.PIPE)

field = "source_uid"
table = "Actions"
inc = 10000
bottom = 0
top = int(1e9)

def query():
    for i in range(bottom, top, inc):
        process.stdin.write("""select %s from %s where %d <= id and id < %d;\n""" % (
            field, table, i, i + inc))
    process.stdin.close()

t = threading.Thread(target=query)
t.start()

c = collections.Counter(process.stdout)

for (value, count) in c.most_common(50):
    print value.rstrip('\n'), count