I’m working through a problem set from CMU’s public db systems course. I have the following two tables: Order Id CustomerId EmployeeId OrderDate RequiredDate ShippedDate ShipVia Freight ShipName ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry 10248 VINET 5 2012-07-04 2012-08-01 2012-07-…
Tag: optimization
PostgreSQL check if values in a given list exist in a table
Given below table in Postgres: id some_col 1 a 1 b 2 a 3 a I want to get output as id and true (if at least one row with that id is present in the table) or false (if no rows with that id are found in the table). For example where id in (1, 2, 3, 4, 5):
How to optimize datetime comparisons in mysql in where clause
CONTEXT I have a large table full of “documents” that are updated by outside sources. When I notice the updates are more recent than my last touchpoint I need to address these documents. I’m having some serious performance issues though. EXAMPLE CODE gets me back 212,494,397 documents in 1 m…
Mysql where condition for single SELECT [not all]
I want to calculate the total gross amount of a CLIENT in all stores and in a specific store both in one query. Not repeating the same query twice as I did below and also not using group_by. My tables are: Clients and Orders Expected result: My query is Answer SUM with CASE WHEN for given store to get gross
refactoring sql while loop to regular inserts
I am inserting parent records and child records at the same time in a stored procedure. Rather than have outside code make nested calls to create each parent and then each child of that parent (which is even slower than my current approach), I am giving the sql a comma separated list of child types that I put…
Is there a better way to execute this SQL query?
I have written this SQL query to get data for each customer in my database. As you can see, I’m trying to get the total of unpaid orders and the total of orders to my query. My goal at the end is to get only the users with unpaids orders (I think i will have to make it with a
How to optimize min and max to find highest score sum
This problem involves finding two min-max criteria filters to generate the highest score sum in a dataset. I have a dataset, with 3 columns. x, y, score, with over 1 million of rows. x y score 3.6 1.2 -5 4.2 1.2 -4 1.2 30.2 1 2.9 6.8 6 3.1 5.8 7 0.1 15.8 7 The data may or may not
Syntax performance of INNER JOIN
Is the performance of both these examples the same? Example 1: Example 2: I am using example #2 at the moment since I am joining 15+ tables, each table with many unnecessary columns and many rows (1 million+) Answer Oracle is smart enough and does not take all columns from table 1 and join them with all colum…
What is difference between `flag is TRUE` vs `flag = TRUE` in MySQL?
I ran the following queries in MySQL – Time taken = 1 second. vs Time taken = 66 ms. I have indexes on (valid, priority) and (valid). Why is there such a huge difference? What is the difference between Is TRUE vs = TRUE ? Answer As per the Mysql Doc for IS operator IS boolean_value Tests a value against
Which one is the most optimal way to delete and update query in mysql?
Suppose, I need to delete or update some id informations from my database, which would be the best way to do it? Should I first find out that if that id exists or not? Or should I update the data then …