Slow query Optimization

Question

Currently, Maria DB queries are getting slower and slower.

UPDATE `table1` 
SET `ANS` = (SELECT COUNT(*) 
             FROM `table2` 
             WHERE `uid` = 1011 AND `opt` = 1) 
WHERE `uid`= 1011;

The number of rows in table2 is about 40 million. So this query seems to be slow.

How can I optimize it?

Answer 1

You select the count of rows in table2 matching a particular uid and opt. Have an appropriate index for the lookup:

create index idx2 on table2 (uid, opt);

Then you access table1 rows macthing a particular uid. Have an appropriate index for this, too:

create index idx1 on table1 (uid);