turnfire turnfire - 1 year ago 55
SQL Question

PHP & MySQL, efficient way to check for rows many times in quick succession?

I'm facing a challenge that has never come up for me before and having trouble finding an efficient solution. (Likely because I'm not a trained programmer and don't know all the terminology).

The challenge:

I have a feed of data which I need to use to maintain a mysql database each day. To do this requires checking if a record exists or not, then updating or inserting accordingly.

This is simple enough by itself, but running it for thousands of records -- it seems very inefficient to do a query for each record to check if it already exists in the database.

Is there a more efficient way than looping through my data feed and running an individual query for each record? Perhaps a way to somehow prepare them into one larger query (assuming that is a more efficient approach).

I'm not sure a code sample is needed here, but if there is any more information I can provide please just ask! I really appreciate any advice.


@Sgt AJ - Each record in the data feed has a number of different columns, but they are indexed by an ID. I would check against that ID in the database to see if a record exists. In this situation I'm only updating one table, albeit a large table (30+ columns, mostly text).

Answer Source

What is the problem;

if problem is performance for checking, inserting & updating;

insert into your_table
(email, country, reach_time)
values ('mike@gmail.com','Italy','2016-06-05 00:44:33')
on duplicate key update reach_time = '2016-06-05 00:44:33';

I assume that, your key is email

Old style, dont use

if email exists

update your_table set
reach_time = '2016-06-05 00:44:33'
where email = 'mike@gmail.com';


insert into your_table
(email, country, reach_time)
values ('mike@gmail.com','Italy','2016-06-05 00:44:33')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download