Web page database query optimization

Question

I am putting together a web page which is quite 'expensive' in terms of database hits. I don't want to start optimizing at this stage - though with me trying to hit a deadline, I may end up not optimizing at all.

Currently the page requires 18 (that's right eighteen) hits to the db. I am already using joins, and some of the queries are UNIONed to minimize the trips to the db. My local dev machine can handle this (page is not slow) however, I feel if I release this into the wild, the number of queries will quickly overwhelm my database (MySQL).

I could always use memcache or something similar, but I would much rather continue with my other dev work that needs to be completed before the deadline - at least retrieving the page works - its simply a matter of optimization now (if required).

My question therefore is - is 18 db queries for a single page retrieval completely outrageous - (i.e. I should put everything on hold and optimize the hell of the retrieval logic), or shall I continue as normal, meet the deadline and release on schedule and see what happens?

[Edit]

Just to clarify, I have already done the 'obvious' things like using (single and composite) indexes for fields used in the queries. What I haven't yet done is to run a query analyzer to see if my indexes etc are optimal.

Answer 1

Your approach is totally wrong.
There is noting bad in these "trips to the db".

And your attemts to minimize query number at any cost may lead you to slow queries and performance disaster

Answer 2

18 db queries is probably a bit of overkill unless it's some kind of complicated portal; although without knowing 100% what the page is and the server back-end code it's hard to judge.

The main cost of extra query is usually the cost of establishing the database connection for it as well as query round-trip.

For the former, make sure that your back-end supports shared pool of DB connections (I assume you use PHP so I don't have any practical advice, but both Java and Perl have ways of achieving that); and of course make sure one page load reuses the same DB connection for the entire page.

For the latter (less queries), look into:

• Bundling all queries into a single large query with multiple result sets

• De-normalizing your result sets via JOIN and UNION like you already do

Also, consider having a middle tier between your webapp and the DB (memcache, or an app server that caches data).

However, I must say that practically, I'd advise against doing any of the above until you test the app against prod server and benchmark and find the slow points using benchmarks and profiling.

UPDATE: To answer the skeptic in the comment, here's some info on the cost of connections, specifically as related ot mysql

http://mysql-dox.net/Sams-MySQL.Database.Design.and/0672327651/ch14lev1sec3.html (Google cache)

Answer 3

Are you retrieving the same information across multiple pages at all? If you are it may be possible to pass that information from page to page rather than querying the DB every time.

For instance, say you are displaying a users name at the top of every page (like SO does). It may make more sense to pass this information from page to page rather than query the DB for it every time. Kind of an obvious example i know, but I hope it demonstrates what i'm trying to say.

Answer 4

18 queries aren't a problem, provided they are fast and efficient.

However, if you feel that is too many, maybe you should take a look at the larger picture and determine if that page is trying to do too much.