I just moved my site to a different dedicated box.... everything works fine, except for 2 pages, which use a join to compare 2 tables. the code is as follows:
$query = "SELECT * from links1, phpbb_users WHERE links1.username = phpbb_users.username AND status = 0 AND $link_types ORDER BY posttime";

On my old box... he page that has this code took 0.003s to execute... . On my new box, it takes 6.3s.

If I change the code to this, it goes down to 0.003 again:
$query = "SELECT * from links1 WHERE status = 0 AND $link_types ORDER BY posttime";

but then my script is broken.

The only different between the 2 servers is version of php/mysql. Old was running php4/mysql4 new is running php5/mysql5.

Could the versions of php/mysql be responsible for this, or is this something different?

First off: check for indexes. Compare the indexes built on both the old and new databases -- chances are high the new box is missing an index on phpbb_users or on links1; look especially at the username column in both.

(This assumes you have significant data, not just a few dozens or hundreds of rows)

Huzza! I made username field unique, and it started working!

Thanks!

There you go. Its surprising how many software packages create data without creating indexes; sometimes backup and restore are to blame. Whenever performance in a db app seems an issue, check indexes first.

Another tip - use your db's "explain" facility - it'll show you the query plan for a given query and sometimes uncover things not immediately visible.

Is there a guide to indexes somewhere? Im starting to do my own coding work, and I dont totally understand them.