0

Hello guys.

I've an issue with a simple query.
Here we go, that's the code.

UPDATE user_resources AS ures
                LEFT JOIN user_buildings as ub
                ON ub.city_id = ures.city_id
                INNER JOIN building_consumption AS bcons
                ON bcons.resource_id = ures.resource_id
                SET ures.quantity = ures.quantity - abs(FORMULA HERE that requires 
         building level and consumption at lvl 1 [default])
                    WHERE
                (SELECT COUNT(id) FROM building_consumption AS bc2
 WHERE bc2.building_id=ub.building_id)  =
                    (SELECT COUNT(bc3.id) FROM building_consumption AS bc3
                      LEFT JOIN  tmp_user_resources AS ures
                        ON ures.resource_id = bc3.resource_id
                    WHERE ures.city_id = ub.city_id
                          AND bc3.building_id=ub.building_id
                          AND bc3.quantity>0
                          AND IFNULL(ures.quantity, 0) - abs(FORMULA AGAIN);

I'll try to explain a bit.

As you can imagine, this is for a game. Users (players) can has different buildings in different cities.

tab user_buildings 

|id, city_id, buildings_id, level, usage|

A building can produce different resources tab building_production

|id, building_id, resource_id, quantity_h|

but it can consume some resources too: tab building_consumption

|id, building_id, resource_id, quantity_h|

Obviously a building cannot produce if there are not enough resources to consume for his job. That's why I'm trying to compare WHERE SELECT COUNT how many resources it has to consume AND how many resources it can actually consume.

Mysql does NOT ALLOW to subquery same table inside an UPDATE stmt.

Using a cursor + loop is too much slow. I prefer to use different solution.

Temp table could be a solution but my problem now is how to update the temp table without triggers? (UPDATE + SELECT fires triggers and to avoid endless loops mysql block the query, and i can't pause/resume triggers because 

  IF ((@TRIGGER_CHECKS = FALSE)
      OR (@TRIGGER_BEFORE_INSERT_CHECKS = FALSE))
    AND (USER() = 'root@localhost')
  THEN
    LEAVE thisTrigger;
  END IF;

is inside the trigger itself).

I am open to all your suggestions!
Thanks

P.S. The code must be inside a scheduled event.

Jim Tebstone
  • 552
  • 6
  • 13
  • This looks both too complicated, and, while I did not check it, conceptional incorrect. I don't really see how your query relates to what you are describing. Also I doubt you are checking cases like: 2 buildings that consume the same type of resources; or chains (A produces y using x, B produces z using that produced y); or circles (what happens if A produces y using x, and B produces x using y and you don't have x or y). You could do a loop: let each building consume and produce if resources are currently there, no subqueries needed. Mark as "has produced". – Solarflare Aug 04 '17 at 13:51
  • If two building require the same resources, choose one at random or split/reduce at random. Repeat with the unmarked until no new resources are produced. If you have a specific production system (e.g.resources are not shared between different builing types (x will only be used to produce y), fixed output (A can only produce x), fixed chains (x->y->z), ...), you can simplify this a lot, the output might be determined by a simple formula. But it will be very specific to your exact production system (and is why browser games often have a simple production system that CAN be easily modeled). – Solarflare Aug 04 '17 at 13:51
  • Yes, you are right: Actually I'm using a loop and it works perfectly and can manage all kind of situation. But i'd like to optimize with a single query .-) – Jim Tebstone Aug 04 '17 at 14:00
  • Well, possible optimizations will depend on the very specific production model you chose. But just to make sure you understood my loop as I meant it: the loop is not for one building. The loop is meant for every builing that can produce "right now" because it has enough resources. If you e.g. have production chains of at most 3 levels (e.g. x->y->z, f->g->h, k->l->m), you can do all your production of all users at the same time with 3 repetitions. If you have a more complicated system, you may need more repetitions and e.g. marks; but they can be done on all users/buildings as the same time. – Solarflare Aug 04 '17 at 14:21
  • Ye, actually I'm using a query to grab only the "user_buildings" that can produce something, then I loop over them. But now how to update all the users with a single query at this point? Is there an efficient way? – Jim Tebstone Aug 04 '17 at 15:02
  • You expect me to tell you how to write an optimized query without knowing the current (working) query, the table structure, the production system or other effects on resources (e.g. user spending it on something, raiding, ...)? You can probably execute a query for every user if you just remove the specific userid from it. But I would probably use a completely different data model to begin with. E.g. assuming you have a limited number of resources, you could use a column for each, not one row for each. While it is, in theory, nice to be able to easily add new resource types, this won't happen – Solarflare Aug 04 '17 at 16:07
  • often and is a lot slower and more complicated. Your building table could look like `building_id, res1_in, res1_out, res2_in, res2_out, ...`. Or even `building_id, level, res1_in, ...`, so you do not need a level-dependent formula. You could also precalculate the total production over all buildings (n iron and m ore will give y iron in this minute, the next minute and minute 1799 from now, until a new building is added, that might give new scalings. You can also prepare what happens if resources are missing). – Solarflare Aug 04 '17 at 16:07

0 Answers0