skip to Main Content

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

2

Answers


  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.

    Login or Signup to reply.
  2. You can improve speed by specifying columns you need to fetch instead of SELECT * FROM ...

    Also, using the JOIN keyword in SQL queries can often help improve query optimization

    SELECT com_cards_charge.col1, com_debt.col2 FROM com_cards_charge JOIN com_debt ON com_debt.col3 = com_cards_charge.col3 WHERE ...
    

    These two steps can help you, thank you

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search