import time

import MySQLdb

USERNAME = 'root'
PASSWORD = 'pw'
HOSTNAME = 'localhost'

conn = MySQLdb.connect(user=USERNAME, passwd=PASSWORD, host=HOSTNAME)
c = conn.cursor()

print "Creating tables..."

try:
    c.execute("DROP DATABASE testslowdelete")
except MySQLdb.OperationalError:
    pass

c.execute("CREATE DATABASE testslowdelete")
c.execute("CREATE TABLE testslowdelete.t1(mainid INT NOT NULL, PRIMARY KEY(mainid)) ENGINE=MyISAM")
c.execute("CREATE TABLE testslowdelete.t2(mainid INT NOT NULL, data LONGBLOB NOT NULL, PRIMARY KEY(mainid)) ENGINE=MyISAM")

start = time.time()
print "Inserting values (may take a while)..."
for i in range(0, 5000):
    c.execute("INSERT INTO testslowdelete.t1 VALUES(%s)", i)
    c.execute("INSERT INTO testslowdelete.t2 VALUES(%s, RPAD('', 450000, 'a'))", i)
print "Insertion done (%.1f s)" % (time.time() - start)

start = time.time()
print "Testing SELECT"
c.execute("SELECT t2.mainid FROM testslowdelete.t2 LEFT JOIN testslowdelete.t1 USING(mainid) WHERE t1.mainid IS NULL")
while c.fetchone() is not None:
    pass
print "SELECT done (%.1f s)" % (time.time() - start)

start = time.time()
print "Testing DELETE (without join)"
c.execute("DELETE FROM testslowdelete.t2 WHERE mainid = -37")
print "DELETE done (%.1f s)" % (time.time() - start)

start = time.time()
print "Testing DELETE (with join)"
c.execute("DELETE testslowdelete.t2 FROM testslowdelete.t2 LEFT JOIN testslowdelete.t1 USING(mainid) WHERE t1.mainid IS NULL")
print "DELETE done (%.1f s)" % (time.time() - start)