Performance with php and mysql

Question

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:

Is it better to do (a) and (b) in one single function in order to have only 1 query to the database?
Is it better to do (f), count the result and then either do (e) or (g), and in this way getting rid of (c)?
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!

Standard answer: That probably depends on a bunch of things. Try profiling your code - how fast do the various possibilities run under a single user? Try load-testing your code - what difference does it make if you're getting hundreds of requests a second? — Dominic Rodger
– Dominic Rodger, Commented Oct 20, 2009 at 8:39

Rik Heywood · Accepted Answer · 2009-10-20 08:45:40Z

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.

answered Oct 20, 2009 at 8:45

Rik Heywood

14k10 gold badges65 silver badges82 bronze badges

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

Comments

Alexander Kjäll · Accepted Answer · 2009-10-20 08:51:52Z

0

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.

answered Oct 20, 2009 at 8:51

Alexander Kjäll

4,3543 gold badges36 silver badges57 bronze badges

Comments

RSeidelsohn · Accepted Answer · 2009-10-20 08:48:41Z

0

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...

answered Oct 20, 2009 at 8:48

RSeidelsohn

1,2081 gold badge22 silver badges41 bronze badges

Comments

MarkR · Accepted Answer · 2009-10-21 06:45:27Z

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)

Collectives™ on Stack Overflow

Performance with php and mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related