rumblefiz
asked on
php with large mysql result set
here is my situation:
i have a client (travel agency) that has built up thier member database to over 450,000 members (that have requested to be e-mailed of updates etc... - the reason i mention this is because the news groups wouldnt help because they insisted i was doing spam). we have a custom built smtp engine to handle large volume e-mails that we wrote specifically for this client. the member info is stored in a mysql databse on a freebsd box. we ran a test with 15,000 of the e-mails and everything was sent and received in less than 4 minutes (roughly 220,000/hour). i am opening a socket to our smtp server and issuing smtp commands instead of using php's mail function as i don't want to open un-needed connections. so with 15,000 everything worked fine.
then we tried 80,000. php keeps timing out. i executed the sql statement:
SELECT DISTINCT email_address FROM master_list
at the sql command prompt and it returned in a couple seconds.
if i execute the following php code:
mysql_select_db("pvc");
$sSql = "SELECT DISTINCT email_address FROM master_list";
$iRes = mysql_query($sSql);
echo mysql_num_rows($iRes);
it times out. i guess i could break it into batches of 15,000 but i would like to grab all of the e-mails in one query. does anyone know of a way around this or to handle this?
again, i am NOT spamming. if i were, i would use majordomo or something already written instead of wasting my time coding this from scratch. however, it is critical that we code this our clients specs so that it will function seamlessly with the rest of the site.
thanks
- rumblefiz
i have a client (travel agency) that has built up thier member database to over 450,000 members (that have requested to be e-mailed of updates etc... - the reason i mention this is because the news groups wouldnt help because they insisted i was doing spam). we have a custom built smtp engine to handle large volume e-mails that we wrote specifically for this client. the member info is stored in a mysql databse on a freebsd box. we ran a test with 15,000 of the e-mails and everything was sent and received in less than 4 minutes (roughly 220,000/hour). i am opening a socket to our smtp server and issuing smtp commands instead of using php's mail function as i don't want to open un-needed connections. so with 15,000 everything worked fine.
then we tried 80,000. php keeps timing out. i executed the sql statement:
SELECT DISTINCT email_address FROM master_list
at the sql command prompt and it returned in a couple seconds.
if i execute the following php code:
mysql_select_db("pvc");
$sSql = "SELECT DISTINCT email_address FROM master_list";
$iRes = mysql_query($sSql);
echo mysql_num_rows($iRes);
it times out. i guess i could break it into batches of 15,000 but i would like to grab all of the e-mails in one query. does anyone know of a way around this or to handle this?
again, i am NOT spamming. if i were, i would use majordomo or something already written instead of wasting my time coding this from scratch. however, it is critical that we code this our clients specs so that it will function seamlessly with the rest of the site.
thanks
- rumblefiz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the only problem I see with bowker's answer is that this creates a race condition.
If the DB is updated while he is in the middle of this query someone will either get left out or someone will get mailed twice (or both!). This might not be a problem with a shared server (that was probably on a LAN somewhere) - but it could be a potentially very large problem with the travel agency where the DB is probably in constant turmoil throughout the entire day.
The reason this is a race condition is that the code depends on everything in the DB being just the way it is throughout the entire execution - if it changes bad stuff happens.
Derek
If the DB is updated while he is in the middle of this query someone will either get left out or someone will get mailed twice (or both!). This might not be a problem with a shared server (that was probably on a LAN somewhere) - but it could be a potentially very large problem with the travel agency where the DB is probably in constant turmoil throughout the entire day.
The reason this is a race condition is that the code depends on everything in the DB being just the way it is throughout the entire execution - if it changes bad stuff happens.
Derek
ASKER
- rumblefiz