CODEWITHSUNDEEP
mysqlquery-optimization
sims92
sims92

Reputation: 1

mysql query optimisation on huge record

i'm writing mysql query for checking any existing record in final table, if so then i will update it first and then insert those records which are not present in final table. issue here is using join its taking more time to execute and since using this in aws lambda its timing out means taking more than 15 mins. i'm not using any index here since i couldn't because we have cusomters who uses the unique constraint on different columns.

select count(Staging.EmployeeId)
from Staging 
inner join Final on Staging.EmployeeId = Final.EmployeeId 
where Staging.status='V' 
and Staging.StagingId >= 66518110  
and Staging.StagingId <= 66761690 
and Staging.EmployeeId is not null 
and Staging.EmployeeId <> '' ;

I'm looking in range of 250k records at once and no luck using above query. could anyone suggest how to speed up above query. I cannot use index, so looking for other option to optimize above query. thanks in advance

Upvotes: 0

Views: 50

Answers (2)

Rick James
Rick James

Reputation: 142560

"unique constraint on different columns" -- this does not necessarily prohibit adding indexes. You must have some indexes, whether they are UNIQUE or not.

Staging:  INDEX(status, StagingId, EmployeeId)
Final:  INDEX(EmployeeId)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

If any of those columns is the PRIMARY KEY, then my advice may not be correct.

Are the tables 1:1? If not, are the 1:many, and which table is the "one"?

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 563011

Creating indexes to support the search conditions and the join conditions would be the most common and the most effective way to optimize this query.

But you said you can't use indexes. This seems like an inadvisable limitation, but so be it.

Your options are therefore:

  1. Allocate more RAM to the InnoDB buffer pool and pre-cache your table data pages, so your table-scans at least occur in RAM and do not have to wait for disk I/O.
  2. Upgrade your server to one with faster CPUs.
  3. Delete data until your table-scans take less time.

I mean no disrespect, but frankly, your question is like asking how to start a fire with wet newspaper.

Upvotes: 2

Related Questions