20 queries per page is too much

jonki

Hi, I read somewhere in this forum that if I have 20 queries on one page then this maybe a little too much.

I have my search results which publish the logo of all my clients next to each product.
Currently I do a simple query to get the name of the log in each result. The results are all mixed so I am not sure of another way of doing this.

I have considered doing a query at the beginning of the page and putting into arrays the $username and $logoname of my 130 clients and then use this (somehow) to reduce the number of queries on my page. One big one as opposed to maximum 20 small ones.
The other option would be to simply put the name of the logo in each line of products. This would make my largest (and most consulted) table (20.000 entries) one column bigger, but would mean that I could take this info from the same query as I use for the product info.

The page concerned receives over 25000 visits a day and the products are displayed 20 at a time.

I have a feeling that number 2 is the best option but I have heard this is bad programming.

Any suggestions?

Much apreciated

TenaciousC

I would go with number 2. I don't understand why it would be bad programming (but then again, being self taught doesn't always lend me to making great decisions).

What is your table structure like? It might be easier to come up with a good decision if we could see then.

laserlight

Before we can make any suggestions, we need to know what is your relevant database schema. In other words, what tables are involved in the queries, what columns are selected, and how are the tables related?

jonki

Many thanks for you quick replies.

I have 2 tables - one with all the products (in this case cars) and one with all my clients. The products database simply contains all the information on the car (kms, price, age etc) and the clients all the client data.

I know that the tables should be joined (in this case the common factor is the user_id) but as I too am self taught a lot of the info is repeated. This is what I am trying to avoid with the separate queries.

I think restructuring the whole system to join the tables properly is probably the best move, but it has to be done before tonight (when my TV campaign goes on air again) so I think I will not have time just now.

I will go for option 2 and then will read up on joining tables so as to optimise this.

Unless someone thinks that this is a really bad idea.

Thanks

laserlight

If each logo is specific to a client, then it should be stored in the client table. If each logo is specific to a product, then it should be stored in the product table.

If each logo is specific to a client and yet you store it in the product table*, you have duplication of data (assuming clients have more than one product, which is a reasonable assumption, methinks), which is a Bad Thing. From what I infer, this is probably the case with option #2 at the moment.

Use option #2 if you have to due to time constraints, but the long term solution is to normalise your database.

If each logo is specific to a product and yet you store it in the client table, you have multi-valued fields, which is also a Bad Thing.

jonki

Twas as I thought.

It was one of the first tables I wrote while I was just begining and obviously I have learned a lot since then.

I will have to read up on normalising databases - is this also "joining tables"? I have another site written by employees which uses joins and this slows the system down, so I was wondering the benefits of this system.

Thanks a lot for your help, I now have a basis for starting from.

laserlight

I will have to read up on normalising databases - is this also "joining tables"?

Almost the opposite, actually: normalisation separates data into entities based on their relationships and constraints. You can read the PHPBuilder article on Database Normalization and Design Techniques for an introduction to the topic.

I have another site written by employees which uses joins and this slows the system down, so I was wondering the benefits of this system.

That's an unfortunate side effect: the database engine must do more work to compute the joins. If the join columns are not indexed, you should consider making them indices. If necessary and feasible, you could even cache the result of a join (so you avoid data duplication in the actual tables, but accept it for the cache as a trade-off for efficiency).

jonki

Thanks a million!!! You should change your name to PHP Angel, you've helped me so much over the years.

It is very much appreciated.