search

Home  >  Q&A  >  body text

php - Is there any performance problem if the sql statement is written like this? ? How to improve? ?

Multi-table joint query, subquery, conditional query, sorting

SELECT
  ac.*,
  c.car_number,
  c.car_name,
  cs.car_source,
  cb.brand_name,
  cm.car_model,
  ct.car_type,
  c.number_plate,
  (
  SELECT
    COUNT(ar.auction_record_id)
  FROM
    csp_auction_record AS ar
  WHERE
    ac.auction_car_id = ar.auction_car_id
) AS auction_count
FROM
  csp_auction_car AS ac
INNER JOIN
  csp_car AS c
ON
  ac.car_id = c.car_id
INNER JOIN
  csp_car_brand AS cb
ON
  c.car_brand_id = cb.car_brand_id
INNER JOIN
  csp_car_source AS cs
ON
  c.car_source_id = cs.car_source_id
INNER JOIN
  csp_car_type AS ct
ON
  c.car_type_id = ct.car_type_id
INNER JOIN
  csp_car_model AS cm
ON
  c.car_model_id = cm.car_model_id
WHERE
  ac.auction_s_time <= CURRENT_TIMESTAMP AND ac.auction_e_time >= CURRENT_TIMESTAMP
ORDER BY
  ac.auction_car_id
DESC
LIMIT 0, 10

Related data sheet:

csp_car_brand, vehicle brand table:

csp_car_type, vehicle type table:

csp_car_source, vehicle source table:

csp_car_model, vehicle model table (specific model of the brand):

#csp_car, vehicle table (this table has too many fields, only some are listed)

csp_auction_record, auction record table:

Is there any performance problem with this sql statement? ? How to optimize? ? Should I use PHP to split it into simple SQL statements and then combine the results, or what? ?

高洛峰高洛峰2818 days ago641

reply all(1)I'll reply

  • phpcn_u1582

    phpcn_u15822017-05-24 11:32:34

    Since you asked this, it means that you know there is a problem with your sql. The problem is that the csp_auction_record table is scanned as many times as there are records in the csp_auction_car table. It's scary to think about it, but I do see a lot of people like to write it like this. Also, try to write the where condition in the subquery. The amount of data in the ac table will be reduced a lot, and the subsequent association will be faster.
    Can be changed to the following, the csp_auction_record table is only scanned once

    SELECT ac.*,
           c.car_number,
           c.car_name,
           cs.car_source,
           cb.brand_name,
           cm.car_model,
           ct.car_type,
           c.number_plate,
           IFNULL(ar.count, 0) AS auction_count
      FROM (select *
              from csp_auction_car
             WHERE auction_s_time <= CURRENT_TIMESTAMP
               AND auction_e_time >= CURRENT_TIMESTAMP) AS ac
     INNER JOIN csp_car AS c
        ON ac.car_id = c.car_id
     INNER JOIN csp_car_brand AS cb
        ON c.car_brand_id = cb.car_brand_id
     INNER JOIN csp_car_source AS cs
        ON c.car_source_id = cs.car_source_id
     INNER JOIN csp_car_type AS ct
        ON c.car_type_id = ct.car_type_id
     INNER JOIN csp_car_model AS cm
        ON c.car_model_id = cm.car_model_id
      left outer join (select auction_car_id, count(*) count
                         from csp_auction_record
                        group by auction_car_id) ar
        on ac.auction_car_id = ar.auction_car_id
     ORDER BY ac.auction_car_id DESC LIMIT 0, 10
    
    

    reply
    0
  • Cancelreply