Abelar: Life_in_4k: it’s a table for storing Experience Point transactions – when gamemasters award players experience in possible several different kinds or run code to spend it, a row’s added with the player’s id, how much was awarded/spent in a floating point, when it occured, who did it, what reason, etc.
Klamet: And whether it was awarded or spent.
Chasteen: Sub question, how much resources should I give the vm?
Walke: GraFfiX_: This is loosely true: For planning – competent hardware, competent mysql server config as to buffers and caches, normalized schema design, correct table storage engine choice, proper data types, adequate indexing, competent queries. For troubleshooting – reverse the order. :-
Arcuri: I can design the table easily although I’m not sure what these parameters for float are. never used a float field before. but I ponder if it’s possible to return the TOTAL AVAILABLE EXPERIENCE by retrieving all awarded and subtracting all spent in one query.
Toles: I’d suspect you have bad queries/structure or indexes and adding hardware wont’ help.
Wahpekeche: Ok, so I’m probably f’ed
Clive: Because my other option is to run two queries: one to retrieve awarded XP, the other spent XP, and then just run subtraction in my code.
Demmon: I just thought it would be really cool if I can do it in one and wanted to learn more about SQL at the same time. :
Pianalto: Volund: you should probably stick to an int column for experience. if you’re gonna have fractions of points, use decimals https://dev.mysql.com/doc/refman/5.1/en/fixed-point-types.html
Lingbeck: GraFfiX_: no. enable your slow query log and start working though them.
Yarrito: Yeah Decimal seems much better.
Nerenberg: Ok, thanks for the info, that’s in my.cnf correct?
Brei: Volund: anyway, if you have spent exp in the same table, making more of an exp transactions table, you could just make spent exp negative and sum the whole column for a user
Matysiak: GraFfiX_: you can do it at runtime but I would enable it in my.cnf too.
Knopp: Life_in_4k: Could do that, sure. and I guess it’s easy enough to retrieve them by ‘awarded or spent’ simply by returning ‘all where xp_amount is less than 0’
Calcara: That IS simpler, I agree.
Fosberg: GraFfiX_: as a point of reference one of my clients has similar hardware specs and is doing 300 queries/sec minimium and performing really well.
Deang: I was doing this all using the game code’s built-in database which turned out to be simply. not the best tool for it for a long-running game. so I’m offloading everything into SQL.
Exline: Volund: anyway, i fixed your initial query with some nifty CASEs. SELECT SUMCASE WHEN xp_type=’GAIN’ THEN xp_amount ELSE 0 END – SUMCASE WHEN xp_type=’SPEND’ THEN xp_amount ELSE 0 END AS total_xp FROM table WHERE player_id=1;
Galligher: I did not know about case. time to go investigate it.
Charter: Tht’s exactly what I was hoping existed.
Tota: They’re very very useful for tricky queries
Coonse: Strathman: SELECT a.* FROM a LEFT JOIN b ON a.id = b.id WHERE b.id IS NULL;
Barney: Volund: also look up the IF statement IFcond,A,B – more readable for yes/no conditions
Wolverton: Danblack: I’m absorbing information but it’s slow. D: there is SOOO much in Mysql and the do***entation is insanely dense.
Ripoll: It’s thorough though! very thorough.
Vertz: Yes. its a lot better than sparce info.
Brackey: Volund: SELECT SUMCASE WHEN xp_type=’GAIN’ THEN xp_amount ELSE 0 END – SUMCASE WHEN xp_type=’SPEND’ THEN xp_amount ELSE 0 END AS total_xp FROM table WHERE player_id=1
Behmer: Er was going to edit that.
Pinchbeck: Volund: SELECT SUMIFxp_type=’GAIN’,xp_amount,IFxp_type=’SPEND’, -xp_amount,0 AS total_xp FROM table WHERE player_id=1 AND xp_type in ‘GAIN’, ‘SPEND’;