5

In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk. For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough. My phone has 8GB of RAM, for instance, and it's just a phone. In-memory access times for data is a ...


3

An alternative: SELECT charges_grouped.booking_no , charges_grouped.room_id , charges_grouped.guest_id , charges_grouped.charge_total , payments_grouped.payment_total , COALESCE(payments_grouped.total_payments, 0) AS total_payments_no_null FROM ( SELECT booking_no , room_id , guest_id , SUM(c.gross_total) AS charge_total ...


1

I assume there are no payments without a charge: SELECT SUM(gross_total) AS charge_total , ( SELECT SUM(payment_amount) FROM payments p WHERE p.booking_no = c.booking_no AND p.room_id = c.room_id AND p.guest_id = c.guest_id ) AS total_payments FROM charges c GROUP BY booking_no, room_id, guest_id Whilst this ...


1

Test CREATE OR REPLACE VIEW VIEW_NAME AS ( SELECT table0.fieldBlah "FieldBlah", --more fields IF(@tmp := (table1.foo or table2.foo IN ('State1', 'State2')), 0, table3.uniqueField) "uniqueFieldName", IF(@tmp, 0, table3.uniqueField2) "uniqueFieldName2", --more of these if statements with the same condition ,but different field FROM ...


1

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier. Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user ...


Only top voted, non community-wiki answers of a minimum length are eligible