timeit_mysql_join_sample.py

import MySQLdb
import itertools
from random import randrange, choice

spot_ddl = """
CREATE TABLE IF NOT EXISTS spot (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  seq INT NOT NULL
);
"""

play_ddl = """
CREATE TABLE IF NOT EXISTS play (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL,
  spot INT,
  seq INT NOT NULL
);
"""

play_spot_ddl = """
CREATE TABLE IF NOT EXISTS play_spot (
  play INT NOT NULL,
  spot INT NOT NULL,
  PRIMARY KEY(play, spot)
);
"""

conn = MySQLdb.connect(host="maria.containers.dev", db="test", user="mysql", passwd="mysql", charset="utf8")
c = conn.cursor()

c.execute(spot_ddl)
c.execute(play_ddl)
c.execute(play_spot_ddl)
c.execute("CREATE INDEX IF NOT EXISTS play_spot ON play(spot)")
c.execute("CREATE INDEX IF NOT EXISTS play_seq ON play(seq)")

c.execute("truncate table spot")
c.execute("truncate table play")

for i in xrange(0, 100000):
  c.execute("insert into spot(name, seq) values (%s, %s)", ('spot_{}'.format(i), i))
conn.commit()

c.execute("select id from spot order by id")
for r in c.fetchall():
  for i in xrange(0, 10):
    c.execute("insert into play(name, spot, seq) values (%s, %s, %s)", ('play_{}'.format(i), r[0], i))
conn.commit()

def join_by_sql(seq):
  c.execute("""
    select
      *
    from
      play p
      inner join spot s
        on s.id = p.spot
    where
      p.seq = %s
    limit
      100
  """, (seq,))

%timeit -n100 join_by_sql(randrange(0,10))

c.execute("truncate table spot")
c.execute("truncate table play")

for i in xrange(0, 10000):
  c.execute("insert into spot(name, seq) values (%s, %s)", ('spot_{}'.format(i), i))
conn.commit()

for i in xrange(0, 100000):
  c.execute("insert into play(name, seq) values (%s, %s)", ('play_{}'.format(i), i))
conn.commit()

c.execute("select id from play order by id")
for p in c.fetchall():
  sql = "select distinct id from spot where id in (%s)"
  args = [randrange(0, choice([10,100,1000,10000])) for i in xrange(0,5)]
  in_p = ', '.join(itertools.repeat('%s', len(args))) 
  sql = sql % in_p
  c.execute(sql, args)
  for s in c.fetchall():
    c.execute("insert into play_spot(play, spot) values (%s, %s)", (p[0], s[0]))
conn.commit()

def join_by_sql(num_row):
  sql = """
    select
      *
    from
      play p
      inner join play_spot ps
        on ps.play = p.id
      inner join spot s
        on s.id = ps.spot
    where
      p.seq in (%s)
    limit
      100
  """
  args = [randrange(0, choice([10,100,1000,10000,100000])) for i in xrange(0,num_row)]
  in_p = ', '.join(itertools.repeat('%s', len(args))) 
  sql = sql % in_p
  c.execute(sql, args)

%timeit -n100 join_by_sql(100)

c.close()
conn.close()