Acceptable page generation times

[Crashthatch]

I am developing a web-based game written in PHP using mySQL.
For development I'm using my old Pentium 3, 450Mhz with 256MB RAM running Windows 98, apache and PHP 4.3.9

The page in question takes 0.4 - 1.5 seconds to generate on this system depending on how advanced that person is in the game. This is with only me using it, and accessing one page at a time. The database is also currently almost empty.

When it goes online, there will be many people at various stages in the game- I need to know if the page generation times will still be acceptable. How would I go about working out what the generation times would be on a better server with more in the database, and many people using it at once? (eg. Does 10 people using it at once decrease the speed by 10 times? If I double the number of records in the database, will this halve the speed?)
Also, what would be regarded as acceptable? What sort of time should I aim for?

Any help or knowledge from past experience in this area appriciated. Thanks.

[Barand]

No real way of predicting, especially if you are hosted on a shared server. It will all depend on load at any given time. But there are things you can you if you are using a database.

The time taken by a query is going to be proportional to the amount of data returned so keep it to minimum.

- Don't use "SELECT * .." unless you really do need all columns returning. Instead specify just the column names you need.

- If all you need is record count, don't select the data and get the number of rows, use "SELECT COUNT(*)"

If you are joining tables, don't use
SELECT x.a, y.b
FROM table1 x, table2 y
WHERE x.id = y.id AND x.c = '$somevalue'

use the more efficient syntax
SELECT x.a, y.b
FROM table1 x INNER JOIN table2 y ON x.id = y.id
WHERE x.c = '$somevalue'

Make use of indexes on your columns to speed up queries.

[Crashthatch]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]If you are joining tables, don't use
SELECT x.a, y.b
FROM table1 x, table2 y
WHERE x.id = y.id AND x.c = '$somevalue'

use the more efficient syntax
SELECT x.a, y.b
FROM table1 x INNER JOIN table2 y ON x.id = y.id
WHERE x.c = '$somevalue'[/quote]

Thanks (for this bit especially). I've been using the first way all the time. I'll go read up on this (for joining more than 2 tables at once), and try to change most of the queries.

As for selecting only the fields needed- I've been doing this where it would have the most effect, but not for if I need over (about) 50% of fields. Are you [i]always[/i] better only getting the fields you need? Say I have a table with 20 fields and I need 18, would it be faster to select all the 18 fields individually, or use *?

And I really meant on a dedicated server. I'm going to start off on shared, but I imagine I'll have to go to dedicated fairly quickly if I get a lot of users. Is there any way of predicting the times if I say I'm going on a dedicated server of a certain spec?

[Barand]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Are you always better only getting the fields you need? Say I have a table with 20 fields and I need 18, would it be faster to select all the 18 fields individually, or use *?[/quote]

Yes, but if your search only returns a few rows you aren't going to notice but those extra could make a difference with hundreds of rows, especially if they are TEXT/BLOB fields.