Volund: SELECT.

 
Borchert: Sliney: https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

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.

Stelb: GraFfiX_: http://dev.mysql.com/doc/refman/5.6/en/execution-plan-information.html and http://dev.mysql.com/doc/refman/5.6/en/explain.html

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’;