250 select vs 1

I think I know the answer to this question but wanted another opinion on it. So I have inherited an e-mailing script that sends out X number of mails every time it executes. It currently does things (IMO) in an odd way by

SELECT emailid, emailTo
FROM email
ORDER BY
    dateadded ASC, priority ASC, emailDomain.DESC
LIMIT 250

Then runs this through a PHP loop grabbing out the con开发者_如何转开发tent by

SELECT subject, message, emailHeaders
FROM email
WHERE emailid = {$id}

The message and headers are full e-mail headers (with HTML) so quite a lot of content, now I think it would be more efficient to select out this content on the initial select rather than hit the DB 250 times requesting each mail individually. So changing the main query to

SELECT emailid, emailTo, subject, message, emailHeaders
FROM email
ORDER BY
    dateadded ASC, priority ASC, emailDomain.DESC
LIMIT 250

To me that feels better, I have run the query and it is not significantly slower than selecting without all the content, but would it be better asking for the content by id (indexed) 250 times? If anyone has some stats on this or opinions I would appreciate the input.

Thanks

---

It's better to get all the data in single query. You don't refer to subject, message nor emailHeaders in WHERE and ORDER BY clause so the data is not being processed. Also it's better to execute one query because of:

• possible locking issues with 250 selects
• server has to spend time on receiving/parsing/processing 250 queries instead of one

---

A database use files to store information. Most of the time what happens, is that your SQL client create a connection to the SQL server, asks things ("requests"). The server performs the query, reads the files, and sends back the corresponding results. Then the application that made the request (your mailing application) use the info.

It's waaaay more efficient to make the least requests to the DBMS.

Get the whole resultset, and loop through it in your application. (This is where a foreach-loop is handy.)

---

Are there cases when just a small part from all the 250 emails are sent?

I think that is the only case where the max amount of 251 server hits is possibly debatable. In rest, your proposal looks perfectly legitimate