Performance with php and mysql

Question

Hi, I'm developing a site and I have some doubts about the best approach to query the database. In general, when I load a page, several data has to be fetched from the database. I'm not sure if I'm doing it the best way. So right now and as an example I call the following functions when the page is rendered:

(...)
(a) -> getAuthorName()         // 1 db connection - 1 db query
(b) -> getAuthorLocation()     // 1 db connection - 1 db query
(consider that here it could come more info the author....)

(...)
(c) count<- countNumberBooks() // 1 db connection - 1 db query
(d) if(count == 0)
(e)//do something
else
(f) books<- getBooksByAuthor() // 1 db connection - 1 db query
(g) renderBooks...

Given this example, my questions are:

1. Is it better to do (a) and (b) in one single function in order to have only 1 query to the database?
2. Is it better to do (f), count the result and then either do (e) or (g), and in this way getting rid of (c)?
3. Or, is it better to load everything we need from the database in one step at the beggining, fetch the data into some kind of structure, and then read it as we render the page?

Thanks!

Answer 1

You'll have to test and measure, as it will depend on all kinds of factors about your server, your database schema etc etc

However, unless you are publishing to a site that is ultra ultra busy, the real answer is that is just doesn't matter. Do whichever solution is easier to maintain and if you start to see performance being a problem with your site, profile it to find out what the problem is.

Answer 2

This might not completely answer your question, but be helpful anyway: I'd prefer doing a "SELECT * FROM authors WHERE id = $author_id" and fetch the result as an object (mysql_fetch_object(..)). This way you can refer to any author data you might need without doing a new SQL query every time:

$sql = "SELECT * FROM authors WHERE id = $author_id";
$sth = mysql_query($sql);
if ($author_obj = mysql_fetch_object($sth)) {
  echo "Author name: " . $author_obj->name;
}

etc...

Answer 3

Some quick answers:

1) Yes it's better to fetch as much data as possible from one row when you can, and not split it up.

2) this depends a bit more, if you always are going to use the data you fetch, then it's better to fetch it, and do the count in php.

3) this is a bit to complex to have a general answer to, you might want to look at some sort of ORM.

Answer 4

In general it's better for performance to get more info from the database in fewer queries. However, this defeats code reuse and maintainability in some cases, so you'll have to make a trade-off.

Also don't use antipatterns like UNIONing unrelated info from completely different tables just to save one query. That helps nobody in the long run.

What you should definitely not do is have a loop of queries. But simply issuing quite a lot (maybe 100) of easy one-off queries, isn't all that bad, particularly if your database has low latency and your pages aren't hit too often.

All the queries you do issue should probably use the same connection, as connecting to a database is pretty cheap, but not free (in particular, it requires another round-trip)