Nested SQL queries too slow
<p>I have following code, where I execute another query within the loop of result set from first query
Table1 has 35K records, while table2 has 4M</p>

<pre><code>db = MySQLdb.connect("localhost","root","root","test" )

cursor1 = db.cursor(MySQLdb.cursors.DictCursor)
cursor2 = db.cursor(MySQLdb.cursors.DictCursor)

sql = 'select * from table1 limit 2'

cursor1.execute(sql)
results = cursor1.fetchall()

for row in results:
   sql2 = 'select * from table2 where t1 = '+row['t1']
   cursor2.execute(sql2)
   result2 = cursor2.fetchall()
   for row2 in result2
     #do something
</code></pre>

<p>For each iteration and each query, the process seems to be waiting. I tried profiling with cProfile and got one of the following output</p>

<pre><code>ncalls  tottime  percall  cumtime  percall filename:lineno(function)
3   21.529    7.176   21.529    7.176 connections.py:274(query)
</code></pre>

<p>How to debug this issue? I am quite new to python.</p>

<p>1)Use left join for table1 to get desired results from table2</p>

<p>2)Fetch results and do something you want.</p>

<p>3)* if you don't have indices, add them(just in case) </p>

<p>Some advice: SQL is created on union theory. However, Cursor is set up for looping. So if you use for loop too many times. It will cause your commands to slow down dramatically. Optimization is tricky . Basically, using built in function will improve performance(unless some complicated situations)</p>

<p>Try to solve problems based on union theory( Join/Merge/..) instead of some scripting languages mode here and you will accelerate accelerate your scripts a lot.</p>

python mysql