drastically increase loading of trainer page

richardfoltin commented 6 years ago

Make sure that you have index on the following fileds: trainer.name gympokemon.trainer_name gympokemon.pokemon_uid gymmember.pokemon_uid

Optimized SQL query of loading trainer data

Description

Modifications in the query

Motivation and Context

On bigger databases, trainer page loaded very slowly

How Has This Been Tested?

Tested on my configuration with 32000 rows in trainer table, and 630000 rows in gympokemon table. Query ran about 4-6 times faster

Screenshots (if appropriate):

Types of changes

[x] Bug fix (non-breaking change which fixes an issue)
[ ] New feature (non-breaking change which adds functionality)
[ ] Breaking change (fix or feature that would cause existing functionality to change)

richardfoltin commented 6 years ago

The query originally resulted 0 in maxCP if the trainer did not have any pokemon in gyms (also only counted the cp of the pokémon in gyms). I left it this way - assuming this was intended - but it can be fixed easly

michikrug commented 6 years ago

First query Current code: 0.0635s Your code: 1.1509s (>1s in first run, second one seems to be cached and is super fast 0.0005s, but this seems to be valid for some seconds and then again takes way longer then the current code)

Second query Current Code: 0.0110s Your code: 0.0047s

Third query Current Code: 0.0087s Your code: 0.0045s

In summary, the current code is always giving me faster responses of the XHR call of aru.php in my setup. Which is due to the first query taking way more time.

~500ms vs. >1s

1.200 gymmember 120.000 gympokemon 10.000 trainer

Obihoernchen commented 5 years ago

Is the API live again? :-O

michikrug commented 5 years ago

No, but we now can MITM the app and extract proto infos from real devices

brusselopole / Worldopole