CODEWITHSUNDEEP
mysqlselecttemp-tables
Sharanu Akkur
Sharanu Akkur

Reputation: 9

fetching the currenct financilal year data matching with previous year data by email,pan and mobile

I have more than 1,000,000 data rows in one table (example table name is :test1) and running below query and its taking more than 10 seconds to load the data so can you any one plz guide us to improve the load speed or any temp table can create and use it?

My Query

select distinct(email),'Prev Year Exist' as status 
from test1 
where created_date>='2024-04-01' and created_date<='2025-03-31'
AND ((pan in (select pan from test1 where created_date>='2023-04-01' and created_date<='2024-03-31'))
OR (mobileno in (select mobileno from test1 where created_date>='2023-04-01' and created_date<='2024-03-31'))
OR (email in (select email from test1 where created_date>='2023-04-01' and created_date<='2024-03-31')));

=============================================== its very slow to load the data so it should load faster using my query

Upvotes: -4

Views: 46

Answers (1)

Amirreza Karimyrad
Amirreza Karimyrad

Reputation: 141

You don't need to create multiple subqueries, in your case, a simple join makes a lot more sense since all the subqueries have the same condition. I would go with the query below

SELECT DISTINCT t1.email, 'Prev Year Exist' AS status
FROM test1 t1
LEFT JOIN test1 t2 ON (
    (t1.pan = t2.pan OR t1.mobileno = t2.mobileno OR t1.email = t2.email)
    AND t2.created_date >= '2023-04-01' AND t2.created_date <= '2024-03-31'
)
WHERE t1.created_date >= '2024-04-01' 
  AND t1.created_date <= '2025-03-31'
  AND t2.email IS NOT NULL;

Also for more efficiency you can create an index on the created_date column as well

Upvotes: 1

Related Questions