Currently, I’m building an app (API) for employee management with Laravel! I will try and explain the process step by step so you can understand what I want much easier! When you create/register an Employee you have to assign how many hours will work during the week. Example: After the employee is created he can start writing the hours he
Tag: performance
SQLite: optimize query with union
This is my table definition: And this is my query + plan: So the index is used, but there is still a scan and a b-tree involved in the order by. I tried getting rid of them by adding more indexes, but I don’t get it working. Any ideas of an index which gets rid of the scan? Answer Your
Executiung performant SQL queries equivalent to “nested deletes”
Consider the following ERD for Order, Item and Article entities: I’d like to delete Orders satisfying a complex condition, and then delete Items associated to those Orders, and finally delete Articles associated with those Items. Cascade deleting from Orders to Items is possible as Order is a parent of Item. But cascade deleting from Item to Article isn’t possible as
Which metrics to compare when evaluating SQL query performance?
I recently watched an online course about oracle SQL performance tuning. In the video, the lecturer constantly compares the COST value from the Autotrace when comparing the performance of two queries. But I’ve also read from other forums and websites where it states that COST is a relative value specific to that query and should not be used for an
Order by select count(*) and LIMIT is very slow
I have this query in my program, when I do some sorting with select count(*) field from the query, I dont know why, it very slow when running that query. The problem is when i do some ordering from posts_count, it run more slower than i do ordering with the other field. Here’s the query: select ‘tags’.*, (select count(*) from
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):
Query Search Algorithm using priority array and ignoring conditions
I am facing this problem at my companies logistics: Given array of mixed values ordered by priority ASC (v1 is more important than v2, … etc) I need to search values in a table t like this: The 3 dots in query are the fixed conditions If I cant find any value from query, perform same query ignoring the least
How to store number in shortest possible size?
I will be adding comments into my website and I want to have a tree structure so each comment can have a parent. This creates a problem when retrieving comments because each comment would have to be traversed for children and this is unacceptable when it comes to database storage. There are various solutions to this well known performance problem
Multiple tables joined to a table via single column
I am trying to create query, on below scenario. with my skills I am able to join Table A,A1,B and A,A1,C and A,A1,D individually and union them. Is there any better way to achieve same. I am using Oracle as Database. Answer It all depends on what they mean and if you need to know the columns the values are
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