I have a query that involves a join on three tables. Let these tables be A, B and C. A and C have a primary key and they are joined through an equi-join to B using 2 foreign keys that B has. The optimal indexing strategy would be to use 4 indexes on all 4 keys. However I am constrained
Tag: query-optimization
Need to group records by consecutive date SQL
I’m building a query in order to build consecutive dates which is create table #consecutivedates ( sgid nvarchar(max), metric nvarchar(max), targetvalue nvarchar(max), startdate …
SQL Server performance optimization
I have table to store files. I store SHA256 hash in a column to uniquely identify a file. Before inserting new file I check for duplicate file using the hash and if it exists already I don’t insert. For example my query can be: Right now there are only a few hundred files. When this number grows to a few
One if or multiple where?
Let’s say, I want to set a value to multiple rows depending on the value of another cell. So, I can make this in 2 ways: Select all items and set different values using IF: Update `table` set `a` = …
My query works but I need to make is scalable. I need to remove unions but not sure how to proceed
I need a list of the number of times a file has processed through our system. For example in week 1, for a given warehouseID, x number of files have processed 1 time, x number have processed 2 times, …
How do I select the max(timestamp) from a relational mysql table fast
We are developing a ticket system and for the dashboard we want to show the tickets with it’s latest status. We have two tables. The first one for the ticket itself and a second table for the …
Is it possible to improve the performance of this subquery?
For my application I have a topic table and a vote table. The vote options are -1, 0, and 1. I’m trying to find the amount of times each topic was voted -1 and 1. So to find out, I am doing many …
How to optimize MySQL select query or make it faster
I have a select query, that selects over 50k records from MySQL 5.5 database at once, and this amount is expected to grow. The query contains multiple subquery which is taking over 120s to execute. …
Optimize MySQL InnoDB query for max, count
I have an MySQL InnoDB table with 5.7M rows and 1.9GB size: +——————-+———+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra …
Query slow when use count(*) and inner join
My query is taking around 5 seconds to run. It returns almost 5000 records. If I remove the Select Count(*) …, it runs very fast, but I need this data. Is there a way to improve this query? Thanks Answer Indexes recommended by Gordon Linoff are a must-have. Another thing you could to is move the computation from an inlined