mysql subquery and python sets

Question

so I run my mysql scripts through a python wrapper, the important parts are here though. My goal through this was to see which was faster, to make two separate select DISTINCT() calls and find the ones the ones in list a that are not in list b. I also did this using subqueries.

I found that the subqueries were much faster (as hoped) however, the results it yielded were wrong.

starttime1 = time.time()
LoLs.cursor.execute("SELECT DISTINCT(Id) FROM participants")
drop = cursor.fetchall()
drop = [x[0] for x in drop]
LoLs.cursor.execute("SELECT DISTINCT(Id) FROM history")
exist = cursor.fetchall()
exist = [x[0] for x in exist]
drop1 = list(set(drop)-set(exist))
endtime1 = time.time()
print len(drop1)

print endtime1 - starttime1

this results in:

545594
205.518273115

as expected.

However:

starttime2 = time.time()
cursor.execute("SELECT DISTINCT(Id) FROM participants where Id NOT IN (SELECT DISTINCT(Id) FROM history)")
drop2 = cursor.fetchall()
drop2 = [x[0] for x in drop2]
drop2 = list(set(drop2))
endtime2 = time.time()
print len(drop2)

print endtime2 - starttime2

results in:

427333
185.753734112

It's much faster, awesome, but it results in only 427333 entries instead of 545594.

as a note i did a set difference for the two end lists print len(list(set(drop1)-set(drop2)))

results in: 118261

so there are 118261 unique entries in the first list that are not in the second. I even took some of these and checked if they were in the history table manually, and they were, so the subquery should've caught them.

NOTE March 6th: changed:

 cursor.execute("SELECT DISTINCT(Id) FROM participants where summonerId NOT IN (SELECT DISTINCT(Id) FROM history)")

to

 cursor.execute("SELECT DISTINCT(Id) FROM participants where Id NOT IN (SELECT DISTINCT(Id) FROM history)")

to accurately reflect the real problem.

Community · Accepted Answer · 2017-05-23 12:07:50Z

1

NOT IN queries remove nulls. Try instead:

cursor.execute("""SELECT DISTINCT(Id) 
                  FROM participants p
                  WHERE NOT EXISTS (SELECT 1
                                    FROM history h
                                    WHERE h.Id = p.summonerId)""")

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Mar 5, 2016 at 0:46

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Over a year ago

still the same issue because both id's are required non null. so nulls don't exist

Collectives™ on Stack Overflow

mysql subquery and python sets

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related