Optimizing SQL query with multiple joins for faster performance

Question

com_cards_charge : com_cards_charge table(DB) com_debt : com_debt table(DB)

some of result: Result1 Result2

SELECT 
      * 
   FROM 
      com_cards_charge, 
      com_debt 
   where 
         com_debt.com_debt_cards_charge_id = com_cards_charge.com_cards_charge_id 
     AND debt_Creditor_type in (0, 15) 
     AND com_cards_charge_deleted = '0'  
   GROUP BY 
      com_cards_charge.com_cards_charge_id  
   order by 
      com_cards_charge_id DESC

This query takes more than 6s to load data. The total rows: 21121.

Thanks for helping me

Answer 1

First, start to write your queries using JOIN showing context relation on how A = B. Second, add aliases next to your table names in the FROM clause so you can use the shortened version within the rest of the query. Helps if you are using the same table multiple times in the same query for alternate purposes too.

Next, add the following indexes on your tables

table               index on
com_cards_charge   ( com_cards_charge_deleted, com_cards_charge_id )
com_debt           ( com_debt_cards_charge_id, debt_Creditor_type )

And updated the query with joins and alias use

SELECT 
      * 
   FROM 
      com_cards_charge cc
         JOIN com_debt cd
            on cc.com_cards_charge_id = cd.com_debt_cards_charge_id
           AND cd.debt_Creditor_type in (0, 15) 
   where 
      cc.com_cards_charge_deleted = '0'  
   GROUP BY 
      cc.com_cards_charge_id  
   order by 
      cc.com_cards_charge_id DESC

Also, dont paste images to your posts, EDIT and manually type samples of the data (that is important to show context of what you are trying to get). Same with displaying table structures.