Performance of query

Question

I have an sql query to get values from 4 tables. In my query takes lot of time. I need to simplify the query what i need is i have to display only 50 records. In my table i have 90,000 records. so i deciede to apply batch processing like first select the 50 records from first table and then check with the 3 other tables. if the 50 is satisfied i will display that otherwise i have to continue next 50.

But i don't have idea to implement

  select file_name, 
         A.id, 
         A.reference, 
         user.username, 
         c.update_date 
    from A_Table A, 
         (select reference 
            from B_Table 
           where code = 'xxx'
           group by reference 
          having count(*) > 1) B, 
         C_Table c, 
         D_Table d
   where A.reference = B.reference
     and A.id = c.id 
     and A.code = 'ICG'
     and c.updated_by = d.user_id  
order by 3 
   limit 20;

Answer 1

The query looks fine.

Adding some indexes will help a lot.

Assuming the id columns (A_Table.id and C_Table.id) are already PRIMARY KEY columns, you won't need to index them.

ALTER TABLE A_Table
    ADD INDEX (reference),
    ADD INDEX (code);
ALTER TABLE B_Table 
    ADD INDEX (reference),
    ADD INDEX (code, reference);
ALTER TABLE C_Table 
    ADD INDEX (updated_by);
ALTER TABLE D_Table 
    ADD INDEX (user_id);