[SOLVED] How to speed up sql sub query

How to speed up sql sub query

i'm running a job and its taking too long to run.

I have created a job to update based on the value of multiple tables

UPDATE applicant_scores 
  SET applicant_scores.Age=2.5
where applicant_scores.Applicant_id in 
  (select applicantinfo.subebno from applicantinfo
   WHERE SUBSTR(applicantinfo.DOB,7,4) ='1985')

This should update a column with about 17000 rows, but it's taking too long time.

Solution

Your problem is this:

WHERE SUBSTR(applicantinfo.DOB,7,4) ='1985'

The database doesn't have a way to quickly find all the rows that match that criteria. There's no index. It has to check every row in the database to find the ones that match that expression.

One solution you have is you can add another column to your table, maybe call it dob_year, then is just the year out of that date. Then, you CREATE INDEX applicantinfo_dob_year ON applicantinfo(dob_year). Then change your WHERE clause to WHERE dob_year ='1985')

https://use-the-index-luke.com/ is a great site for learning about database indexes and how to use them properly to make your queries fast.