What Can I be Researching to speed this python scripted mysql-query up?
Question: so I spent a long time on the site yesterday to write this entire script in
python (which I had never before used). I was pleased with the results (it
worked on my tiny test-data set of 10,000 entries), but now that I'm using
production data (many million) it is incredibly slow.

Here's the code:

    
    
    #!/usr/bin/env python
    import sys
    import MySQLdb
    import argparse
    
    
    parser = argparse.ArgumentParser(description="This is used to actually generate matched lists. outputs those that matched")
    parser.add_argument("Database", help="The name of the database you want to run matching against")
    args = parser.parse_args()
    
    
    tablename = str(sys.argv[1])
    mydb = MySQLdb.connect(host='localhost',
        user='root',
        passwd='[REDACTED]',
        db='Data')
    cursor = mydb.cursor()
    
    sql = "SELECT name FROM table1 WHERE md5 IN (select md5 from %s)" %tablename
    cursor.execute(sql)
    
    data = cursor.fetchall()
    for row in data :
        print row[0]
    #close the connection to the database.
    mydb.commit()
    cursor.close()
    

to explain how my tables look:

table1 | name | md5

table2 | md5

I'm sure there's some optimization I could do on the server itself to make
mysql faster, but my suspicion is that there's some ways I could have made
this faster in the code itself. Any suggestions on where to continue
researching this would be appreciated.

Cheers!


Answer: Do something like this:

    
    
    SELECT 
      t1.name 
    FROM
     table1 t1
     INNER JOIN table2 t2 ON
       t2.md5 = t1.md5 
    

And you should also **index the md5 columns** on both tables!

The _in_ operator acts much like a _left outer join_ , so you are giving the
server an enormous amount of data to process when you only want the names of
the md5 columns that match!

