Speed...amount of querys per page?

php4_23

What should be the maximum amount of querys required to display one page? 5- 15??

thanks

Form1

That, of course, depends on how long each entry is. 25-30 is pretty standard for short, one line entries, but you may find that it's a bit long for larger results. Users generally don't like having to press a "next x results" button, but they generally dislike waiting for pages to load, as well, so it is a bit of a balancing act.

(I'm assuming by "query" you mean row.)

php4_23

I don't think I explained it right.
What I am saying is what would be the maxium amount of times you would want to query the database, without the script becoming really slow?

How fast are database querys??
thanks

mooseday

It would depend on a large number of things - the speed of the server, the structure of the database, the type of query, the number of expected rows to be returned, the amount of fields in the row, the number of hits you are expecting, if the database server is the same as the PHP server - the list could be endless.

A count or joined query on a non indexed field on a table with 5000 records could take the same time as 5 simple select statements on a indexed table with 20000 records.

The only real way to tell is to create the page and store the times from the first query to the last query and calculate the time taken for each query - 5 to 10 seconds would be a good limit.

Personally, if you need to do more than 5 queries you should be looking at your page, database and table design - check out creating views or store procedures.

php4_23

Okay, thanks.
I am trying to figure out ways to reduce the number of querys. Currently I am querying about 8 times. I am not sure how I am gonna reduce that ( I am building a forum).
anyways thanks again

mooseday

If you are querying data from a lot of tables but on a single common key think about using a view or a join on the table(s).

superwormy

Again, it's not the number of queries thats important at all.

It's the complexity of the queries and amount of data you're fetcyhing. For some queries / datasets, 1 query could take 10 minutes to execute. For other queries / datasets, you could have 200 queries on the page with no noticeable slowdown.

ultraslacker

What should be the maximum amount of querys required to display one page? 5- 15??

The answer is 14! 😃

Sxooter

It's all about balance. Water, fluid and ever moving wears away the stone...

Sorry, I blacked out for a second. Anyway, what you REALLY wanna do is figure out which queries can get you your result set the fastest.

Sometimes one big query is way faster than a bunch of little ones, sometimes not.

Build your query, then echo it to the screen. Then cut and paste it into whatever tool you databases uses for direct human interaction and explain / analyze it. If you've got a choice between one big honker of a query that runs in 400 ms, and a dozen that each run in 5 ms, then the dozen win.

but maybe over time, as the database is populated, the big honker will stay at 400ms while the little ones will rise to 20, 30, even 50 ms as the database gets bigger and individual queries result in more random I/O than before.

It's not how many queries, and it's not how much that they "cost" in CPU/ I/O. It's how many times how much they cost that counts. The product of the two.

php4_23

thanks

I dont think my querys are really taking that long...they are very simple.
I probably could combine it into on honking query but it would be a mess (and probably alot slower).

Sxooter

Sometimes the big query is a win, sometimes it's a loss. As long as you aren't disconnecting and reconnecting between each query (a common beginner's mistake, I'm not saying you're doing it) then the many little queries are probably fine. If the data are related, and you're going to do some kind of advanced calculation to decide which rows to keep and which to toss, then it's usually better to use one big query.

If the data are not well related, or you're not doing any complex calculations, then often many small queries are better than one big one.

What's nice is to have your app spit out it's SQL queries, and then copy and paste them one at a time into your sql monitor to explain [analyze] the query and see how fast it runs. Running it more than once will show you how caching affects it's performance. Running them all one after the other over and over will show you whether or not you're running out of cache by the time you go round robin.

php4_23

Thanks for the tips. I am gonna be doing to testing to see which one is more effiecient.
Oh and nope, I am not reconnecting each time.