Question 177580 in Mysql Question posted in
The official MySQL documentation can be found here.

Can I optimize a SQL query with multiple joins for faster performance in MySQL?

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

Tags:

2

Answers


  1. 0

    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. 0

    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