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>Nesting queries within a web application is never a good idea.  As you've found, it kills performance.</p>

<p>Try using a single query which joins table1 and table2.  Then, programmatically keep track of when your parent data changes to handle line breaks or display changes.</p>

python mysql