I am trying to see if there is a way to optimize bottom T-SQL code. This is how previous co-worker wrote, and I am not sure if there is a better way to write because it has Select… within Where clause (subquery?). select Employee_Number, DateLoad from dbo.table1 as a where DateLoad = (select min(DateLoad) from… Read More How to optimize this subquery (without using Select inside Where clause)?

I have a two large tables. In the first table have indexed id field with type int. In the second table have indexed classId field with type varchar(50). I need to get all fields from first table which do not have classId in the second. select a.id, a.type, a.path, a.filename from assets a where a.type="folder"… Read More What is best way to compare int and varchar fields in mysql?

I want to add pagination to a data table so I need to count the numbers of row a filtered query will return. My plan is to show only 5-10 pages from current page in each directions (if the pages exist). However some some queries with that has been filter the result might be fewer… Read More How to figure out if a SQL query will return more than X rows?

i have a below query it using index but not yet optimized I don’t know what is the problem can anyone help me with this ? select date(mfd.MFD_FITMENT_DATE) as MFD_FITMENT_DATE, count(*) cnt_fitment FROM MMHSRP_FITMENT_DATE mfd, CUSTOMER_REGISTRATION_DETAILS crd, EMBOSSING_STATION_MAPPING_DETAILS esmd where mfd.MFD_CRD_ID = crd.CRD_ID AND esmd.ESMD_SDM_ID = crd.CRD_SDM_ID AND esmd.ESMD_ESM_ID = ‘9’ AND mfd.MFD_STATUS = ‘0’… Read More can anyone help me to optimize the where clause

I’m trying to learn the ‘order by optimization’ of mysql, so i search the related topic and find the official doc of mysql8 says : A condition for index use is that the index must have the same homogeneity, but need not have the same actual direction. If a query mixes ASC and DESC, the… Read More mysql order by multiple column optimization

I am trying to delete data from table which contains almost 6,000,000,000 records , with where clause. here is the stored procedure I am using and running from command prompt MySQL in windows. DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `clean_table`( ) BEGIN REPEAT DO SLEEP(1); DELETE FROM tablename WHERE exportInfoId<=8479 limit 100000; SELECT ROW_COUNT(); UNTIL ROW_COUNT()… Read More MYSQL delete from large table increase the delete time for each transaction

In my project I have a use case where we need to trigger an API on the first of every month to send gift cards to users via email who have their birthday in current month. I have a SQL query that fetches user information having their birthday in a given month and have not… Read More How to optimise MYSQL query which uses subquery

Suppose I have a table containing various types of fruit and vegetables. Once every 5-10 minutes, I am checking the current price of all items I have listed, and I insert a new record for that timestamp with the current price. The following is an example of such a table (number of rows reduced for… Read More How to get a single record per group from one day ago in a very large MySQL 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). ex: where id in (1,2,3,4,5)… Read More Postgers Exists with where in condition

Ok, so I’ve got two tables: jobs, and job runs. I’m using Postgres. I want to look at 2 periods. 7 days ago until now, and 14 days ago to 7 days ago. For each job, I want a total of the number of runs, and a percentage of successful and unsuccessful runs for each… Read More Calculating totals and percentages for each row, in a time boxed window, for a relation