470 queries to load 1 page...

akillez

is that a nono? 😕 😃

wirenot

There has to be a way to optimize that number of queries. Is there a way to load alot of the stuff into one array and parse through it with PHP ? Just imagine the load on the mysql server when a 100 people are accessing the page.

Why don't you explain to us why you need so many queries per page; maybe we'll be able to help more then.

akillez

What it's about

There are 94 teams listed * 5 queries = 470

I'm pretty pleased that I finally got it working and I do need all the data on 1 page.

I doubt there will be 100 peeps @ the same time on the site though, but still...

If you or somebody else can come up with a better solution 😃 😉

nicolai

Try posting your script (and your database structure). I am very sure that someone in here can help you reduce the number of queries.
470 queries pr. client pr. page sounds wrong to me. =)

MarkR

470 queries per page MIGHT be acceptable.

Ask yourself:

How often do people hit this page? Who hits the page? are they normal (non-admin) users?
How often do your data change?

If people hit the page significantly more often than your data change, perhaps it is worthwhile pre-generating some or all of it, or caching the results somewhere.

But if it only gets hit occasionally, don't worry. 470 queries is unlikely to take more than a fraction of a second on modern hardware. The bottleneck actually could be PHP.

Mark

yelvington

Why does it require five queries per team in order to obtain the necessary data?

akillez

What it's about = db structure & script

The data changes onces a week.

There are approx. 3000-4000 people playing the game in belgium,
so I'm guessing about 500-1000 possible visitors on a weekly base + 1 admin (me). 😃

It needs 5 queries per team because I haven't found a better solution to get the data, check the link for the script...

Roger_Ramjet

As I said in the other thread: you need to redesign your database layout.

When you enter the scores, calculate and store the result at the same time, ie Home win, Away win, Draw.
Add win, loss, draw columns to your team table and update this when you enter the results.
Then calculate and store the various aggregates that you want to display in more columns for each team.

All of this is possible because the actual results are static data - they do not change over time. Calculating and storing this data once is much more efficient than calculating it every time a user wants to view it.

You only ever need to use on-access agregation when the source data is dynamic and rapidly changing, eg stock market prices, hits per minute, or when it is for a one-off report, etc.

akillez

You're right RR, it would be faster and easier to code, I know that 2.
But I'm a bit reluctant to denormalize the table structure...
It happens regularly that the wrong scores get communicated, meaning I have to update the tables manually when I do receive the right scores.
Having that process-data saved statically increases the chances of me screwing things up. 😉

Roger_Ramjet

1 This does not constitute 'de-normalisation'

2 Create a data entry/update page that includes the queries to calculate and store the results, etc. These will only be the same queries that you would otherwise have to include in the data display pages. If the original data is wrong then the output will be wrong not matter when it is calculated.

3 Review this http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html and especially this http://dev.mysql.com/doc/mysql/en/Tips.html.

Of course in the end it is up to you, but if your performance is slow then people will not like your site.