sql help

[peterisgb]

would this work if i put it in a cron file to run every 15 minutes.

i think its coded bad, but this is a weakpoint i'm working on.

 

$q=$db->query("SELECT userid FROM users");
while($r=$db->fetch_row($q)) {
$e=$db->query("SELECT * FROM buildings WHERE userid={$r['userid']}"); 
while($y=$db->fetch_row($e)) {
$gain = rand(100, 100) * $y['goldmine'] * $y['goldmineworkers'];
$db->query("UPDATE users SET gold=gold+$gain");
}
$e=$db->query("SELECT * FROM buildings WHERE userid={$r['userid']}"); 
while($y=$db->fetch_row($e)) {
$gain = rand(100, 100) * $y['farm'] * $y['farmworkers'];
$db->query("UPDATE users SET food=food+$gain");
}
$e=$db->query("SELECT * FROM buildings WHERE userid={$r['userid']}"); 
while($y=$db->fetch_row($e)) {
$gain = rand(100, 100) * $y['sawmill'] * $y['sawmillworkers'];
$db->query("UPDATE users SET wood=wood+$gain");
}
}

[Alan]

The inner loop can be compressed to one while statement - something along the lines of

    $x=$db->query("SELECT * FROM buildings WHERE userid={$r['userid']}"); 
while($y=$db->fetch_row($x)) {
   $gold_gain = rand(100, 100) * $y['goldmine'] * $y['goldmineworkers'];
   $food_gain = rand(100, 100) * $y['farm'] * $y['farmworkers'];
   $wood_gain = rand(100, 100) * $y['sawmill'] * $y['sawmillworkers'];
   $db->query("UPDATE users SET gold=gold+$gold_gain, food=food+$food_gain, wood=wood+$gain");
}

However with say 1,000 users and maybe 5-10 buildings per users, that's 5,000-10,000 queries every fifteen minutes - 20,000-40,000 per hour which I know a lot of smaller hosting providers will not be very tolerant of. Not to mention the fact that given an McCodes base, you will be locking the entire users table for the duration the cron runs which will introduce an element of lag you may have trouble getting rid of in the future.

Thankfully there are far better ways of doing this, a timestamp would work which could reduce this potential ~40,000 Q/hr down to quite possibly 2 figures or less even if you have an active player base; it may even be possible to rewrite the entire set of queries as one by careful use of joins although I'm not in favor of UPDATE with JOIN statements.

Try a timestamp route; your players probably only need be aware of their own stocks, therefore no need to update everybody en-masse. Just update the current player as and when.

[KyleMassacre]

also another way to optimize your query along with what alan is saying is remove the * from your queries. It looks like you are only using a couple columns from your buildings table so just grab those.

It may not be much difference but if you go through ALL of them and use what you need then it all adds up in the long run especially on your minute crons, and globals. I mean with this example you stated your looking at like ~ .00001 difference in speed but plan for the long run

[Alan]

Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%.

Donald Knuth.

[KyleMassacre]

I think I personally would disagree. Especially with my knowledge of programming I try to optimize where I can because some of my stuff may not be written in the best manner. But I'm willing to shave a few thousandths of a second of my query times when I can. And I don't see the problem with not using the * in your queries unless you actually plan on using the entire table.

I'm just trying to think of the long run here because as you add to your programming your adding more queries within fractions of a second with each other. Now if you take a look at 99% of the people's databases here what do you see? userid int (11), userlevel int (11) money, bankmoney, crystals *int (11+). So a lot of peoples db structure IMHO are already using far too much resources than what is actually needed. Is there going to be 2.1 billion users? Probably not, unless someone spammed your reg for a few hours. How about the same about of userlevels? To me sounds like too many Chiefs and not enough Indians.

See this is why I prefer to optimize as you go. most people here aren't making the next fb or twitter but still.

[Veramys]

I first worry about all of the main things and making sure there are no bugs. Then I worry about fine tuning everything. For me it's easier to get everything down and working before making it more efficient.

[Alan]

I cannot help but wonder what type of ""optimization"" is going to be applied to ...

userid int (11), userlevel int (11) money, bankmoney, crystals *int (11+)

Notwithstanding small ""optimizations"", reducing the number for queries from 40K to a few 10's per hour I'd consider key to solving this problem.

[SRB]

I think I personally would disagree. Especially with my knowledge of programming I try to optimize where I can because some of my stuff may not be written in the best manner. But I'm willing to shave a few thousandths of a second of my query times when I can. And I don't see the problem with not using the * in your queries unless you actually plan on using the entire table.

I'm just trying to think of the long run here because as you add to your programming your adding more queries within fractions of a second with each other. Now if you take a look at 99% of the people's databases here what do you see? userid int (11), userlevel int (11) money, bankmoney, crystals *int (11+). So a lot of peoples db structure IMHO are already using far too much resources than what is actually needed. Is there going to be 2.1 billion users? Probably not, unless someone spammed your reg for a few hours. How about the same about of userlevels? To me sounds like too many Chiefs and not enough Indians.

See this is why I prefer to optimize as you go. most people here aren't making the next fb or twitter but still.

That logic probably mattered when we were running on P4 900 processors with 128MB of RAM, but in this day and age, we need only worry about cutting 40k to 10k - not the nano-seconds.

Also, I think I'd trust the logic of Mr. Donald Knuth

[lucky3809]

I would use timestamps,and use proper database integer types and values, such int 10 unsigned and tinyint for smaller fields that has a small value...

here is a helpful chart on integer types:

http://dev.mysql.com/doc/refman/5.0/en/integer-types.html