optimizing query and optimizing table

Question

`CREATE TABLE emailAddress
(
ID int NOT NULL AUTO_INCREMENT,
EMAILID varchar(255),
LastIDfetched int,
PRIMARY KEY (ID)
)

SELECT LastIDfetched WHERE ID=1;    //say this value is x
SELECT EMAILID FROM emailAddress WHERE ID>x && ID<x+100;
UPDATE emailAddress SET LastIDfetched=x+100 WHERE ID=1;`

Basically I am trying to fetch all the email id from the database using multiple computers running in parallel, so that none of the email id is fetched by 2 computer.

What is the best way to do this? there are millions of email id. here for example i have shown that in one query 100 email id is fetched, it can vary depending on the need.

So you are trying to, for example, process id's in across different computers, but don't want any duplicates? — Mike Brant
– Mike Brant, Commented Dec 2, 2013 at 20:57
yes, and i have added a brief description in comments in answer below — user2368055
– user2368055, Commented Dec 2, 2013 at 23:05

Mike Brant · Accepted Answer · 2013-12-02 21:14:26Z

1

My suggestion would be to query by autoincrement ID's. You may not get an exact split of records across candidate computers if there are gaps in you autoincrement system, but this should be pretty good.

One approach is to simply look at the remainder of the autoincrement ID and grab all items of a certain value.

SELECT `EMAILID`
FROM `emailAddress`
WHERE ID % X = Y

Here X would equal the number of of computers you are using. Y would be an integer between 0 and X - 1 that would be unique to each machine running the query.

The con here is that you would not be able to use an index on this query, so if you need to do this query a lot, or on a production system taking traffic, it could be problmematic.

Another approach would be to determine the number of rows in the table and split the queries into groups

SELECT COUNT(`ID`) FROM `emailAddress`; // get row count we will call it A below

SELECT `EMAILID`
FROM `emailAddress`
WHERE ID
ORDER BY ID ASC
LIMIT (A/X) * Y, (A/X)

Here again X is number of machines, and Y is unique integer for each machine (from 0 to X -1)

The benefit here is that you are able to use index on ID. The downside is that you may miss some rows if the number of row grows between the initial query and the queries that retrieve data.

I don't understand your lastFetchedID field, but it looked like it was an unnecessary mechanitoin you were trying to use to achieve what can easily be achieved as noted above.

edited Dec 2, 2013 at 21:14

answered Dec 2, 2013 at 21:06

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

3 Comments

user2368055 Over a year ago

I am working on application for sending mails to all the email id using multiple computers, so the fetching is done by any computer only when the number of email id left with that computer goes below some threshold value. and the number of email id fetched in chunk depends on the throughtput of the computer in sending mail. The number of computers may vary at the runtime

user2368055 Over a year ago

This is done so that when more computers are added the work is scaled,

Mike Brant Over a year ago

@user2368055 It sounds to me like you need to use a queue if you are going to being changing number of computers dynamically.

Collectives™ on Stack Overflow

optimizing query and optimizing table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related