Mgiyuk

How encrypted AWS PostgreSQL RDS works under the hood? I read that when creating an encrypted AWS Postgres RDS, it encrypts the underlying EBS volume created for it, all read replicas, backups, and snapshots as well. Also when I queried and inserted the data into DB, it worked same as unencrypted DB would and gave results in plain text. I have some questions regarding how exactly it is working under the hood. Here they are: A simple value-based search can be performed by encrypting what to search and then search it on the encrypted RDS. But my search in PostgreSQL was also working on JSONB nested objects. How is that achieved? I was able to do a partial search( like query) on a name and address inside a JSONB object. How can a partial search be done on encrypted DB? like I have some JSONB columns in my PostgreSQL and I was able to partial insert on my JSONB objects. Its a cool feature of PostgreSQL but how was it achieved when the whole JSONB was encrypted? PS: I have some knowledge o...

How to optimise this MySQL query? Millions of Rows I have the following query: SELECT analytics.source AS referrer, COUNT(analytics.id) AS frequency, SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales FROM analytics LEFT JOIN transactions ON analytics.id = transactions.analytics WHERE analytics.user_id = 52094 GROUP BY analytics.source ORDER BY frequency DESC LIMIT 10 The analytics table has 60M rows and the transactions table has 3M rows. When I run an EXPLAIN on this query, I get: EXPLAIN +------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+ | # id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | +------+--------------+-----------------+-...