check if row exist in the table before adding it in SQL

Question

i'm building a twitter grabber application using Python , Tweepy and MySQLdb modules

it will be fetching millions of tweets so performance is an issue i want to check if the tweet_id exists before in the table before adding it in the same Query

the table schema is :

  *id* |   tweet_id             |     text
  _____|________________________|______________________________
    1  |   259327533444925056   |     sample tweet1
  _____|________________________|______________________________
    2  |   259327566714923333   |     this is a sample tweet2 

the code that i tried is but it do double Queries :

#check that the tweet doesn't exist first
q = "select count(*) from tweets where tweet_id = " + tweet.id
cur.execute(q)
result = cur.fetchone()
found = result[0]
if found == 0: 
q = "INSERT INTO  lexicon_nwindow (tweet_id,text) VALUES(tweet_id,tweet.text)
cur.execute(q)

making the Tweet_id unique and just insert the tweets , will raise exception and will not be efficient as well ?

so what's the best performing method to achieve this with one query ?

Answer 1

If you make the tweet_id as a primary key (drop field Id), you could use INSERT IGNORE or REPLACE INTO. 2 problems solved in 1.

If you want to keep the Id field, set it as an index/unique and set it to auto-increment. I would steer clear of this approach if I know tweet_id can be used as a primary key.

Hope this helps.

Hari

Answer 2

#check that the tweet doesn't exist first
q = "select count(*) from tweets where tweet_id = " + tweet.id
cur.execute(q)
result = cur.fetchone()
found = result[0]
if found == 0: 
q = "REPLACE  lexicon_nwindow (tweet_id,text) VALUES(tweet_id,tweet.text)
cur.execute(q)

Answer 3

Use INSERT SELECT instead of INSERT VALUES and in your SELECT add a WHERE clause to check that your tweet.id is not already in the table

q = "INSERT INTO  lexicon_nwindow (tweet_id,text) 
SELECT " + tweet.id +" ," + tweet.text +" FROM DUAL
WHERE not exists(select 1 from tweets where tweet_id = " + tweet.id +" ) "

Answer 4

The answer is profile, don't speculate.

I don't mean to be dismissive. We don't know what will be fastest:

• SELECT + (in code) conditional INSERT
• REPLACE INTO
• INSERT IGNORE
• INSERT SELECT WHERE NOT EXISTS...)
• INSERT and (in code) ignore error

We don't know the rate of data, the frequency of duplicates, the server configuration, whether there are multiple writers simultaneously, etc.

Profile, don't speculate.