I find my self sometimes needing to select many items of different tables based on a list of id's or other variables. Given that this list can contain thousands of elements, it is important for me that the filter does…
I have a table (under RDS Postgres v. 15.4 instance db.m7g.large): CREATE TABLE MyTable ( content_id integer, part integer, vector "char"[] ); There is a B-Tree index on content_id. My data consists of 100M rows. There are 1M (0 ..…
We are using AWS Aurora MySQL(8.0.mysql_aurora.3.04.0) InnoDB engine with large instance(16GB RAM). One particular “SELECT” query running against one large table (250GB - not partitioned) in the "reader" instance which does some calculations including min,max,window functions, group by, etc. This…
I need to select all records for the current day in CST, however the timestamp fields in the table are all in GMT/UTC. The server's timezone is GMT. Getting all records for the current day in GMT is very fast…
Trying to debug a slow query: SELECT IFNULL(SUM(bt.transactionQuantity * bpd.unitPrice), 0) FROM batchTransaction bt LEFT JOIN batch b ON b.id = bt.batchId LEFT JOIN batchPricingDetails bpd ON bpd.id = b.batchPricingDetailsId WHERE (bt.productId, bt.dateCreated) IN ( SELECT productId, MAX(dateCreated) FROM batchTransaction…
I am seeing lot of this wait event in my AWS Aurora MySQL (8.0.mysql_aurora.3.04.0) and the documentation says "this event indicate an increase in workload activity. Increased activity means increased I/O", which causes spike in CPU usage. Note, the underlying…
I'm currently developing a SOAP service with several operations. Requests and responses are saved in a table called "message". Each Request and each Response is its own entry in the table and each Request/Response pair has a corresponding correlation_id. I…
I have a query like this: SELECT DISTINCTROW customers.id, customers.customer_name FROM customers INNER JOIN rentals ON customers.id = rentals.id_customer INNER JOIN rentals_billing ON rentals.id = rentals_billing.id_rental INNER JOIN vehicles ON vehicles.id = rentals.id_vehicle WHERE (rentals_billing.customer_id_facsimile IS NULL AND rentals_billing.rental_payable =…
I have the following query to display the performance of a machine in 8-hour segments (although it can change to 1 hour, 1 week, 1 month, etc.). My table has 2 million records, and the query takes 10 seconds to…
I have a PHP script that use 4 separate query to read the last 25 rows from one single mysql table (so a total of 100 rows). Example: SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'john'…