Global Query

[SHAD]

I was thinking, this mccodes global query seems pretty big and selects alot of stuff, so would it not be better to get rid of the global query and only select stuff which is needed on each file, or am i missing something?

 

global $jobquery, $housequery;
if($jobquery)
{
$is=$db->query("SELECT u.*,us.*,j.*,jr.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN jobs j ON j.jID=u.job LEFT JOIN jobranks jr ON jr.jrID=u.jobrank WHERE u.userid=$userid");
}
else if($housequery)
{
$is=$db->query("SELECT u.*,us.*,h.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid LEFT JOIN houses h ON h.hWILL=u.maxwill WHERE u.userid=$userid");
}
else
{
$is=$db->query("SELECT u.*,us.* FROM users u LEFT JOIN userstats us ON u.userid=us.userid WHERE u.userid=$userid");
}
$ir=$db->fetch_row($is);

[Dayo]

There was a post similar to this by a_Bertrand ages ago if I remember correctly I'll try and dig it out when I get hone

[SHAD]

still wondering what kind of difference it would make?

[Dominion]

still wondering what kind of difference it would make?

Only you know how your site is setup. In general you will always get the answer "select what you need", but that's not helpful if all you're doing is selecting everything on a per page basis.

[a_bertrand]

There is a few things to take into account:

- Reduce the number of queries => so it's better to query a bit more columns (even useless most of the time) instead of doing multiple queries.

- Getting more columns can have a huge impact if your DB server is not running on the same machine as the HTTP server (and therefore your queries will go over the net) if it's on the same, you will see little to no difference.

- Also what counts is to have very quick queries (like selecting data from their key) instead of doing slow queries. You can easily have pages which 100 queries and are yet very fast where you could on the other side have 1 query which kills your server if badly designed.

So no there is no simply answer and your code / sql / tables must be checked and optimized regarding your own personal needs. However from what I saw on those forums very few of you really know how powerful SQL is nor are really able to optimize their database design / queries. If you need some help then we need to have full details and... it may require quiet some work to really optimize such issues for you.