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
