我在prestashop1.6上工作，mysql5.6数据库的容量超过7gb。最大的问题是，当我想看到我的订单在我的prestashop它需要40到2分钟，有时它卡住了。
我提到我们处理超过80000个订单，我们有大约200000个产品。我们尝试了许多优化，比如索引化和最大化子查询。
我们有更好的时间，但仍然很慢。我试着直接在mysql上执行这个请求，但是时间很不稳定，有时是50秒，有时是200秒，差别很大。

/*Request prestashop*/
    SELECT SQL_CALC_FOUND_ROWS a.id_order ,invoice_number,address.id_country 
     AS id_country, total_paid_tax_incl,payment, a.date_add 
     AS date_add , a.id_currency, a.id_order 
     AS id_pdf, CONCAT(LEFT(c.firstname, 1), '. ', c.lastname) 
     AS customer, osl.name 
     AS osname, os.color, 
     IF((
      SELECT so.id_order 
       FROM ps_orders so 
       WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order 
       LIMIT 1
      ) > 0, 0, 1) AS new, 

     country_lang.name 
     AS cname, 
     IF(a.valid, 1, 0) badge_success,a.total_products,a.reference 
     AS reference,

/*Override request pesonalized*/

(
SELECT GROUP_CONCAT( DISTINCT pr.product_group SEPARATOR ", ") /*DISTINCT*/
    FROM ps_order_detail dod 
    LEFT JOIN ps_product pr ON (pr.id_product = dod.product_id) 
        WHERE dod.id_order = a.id_order LIMIT 1 
) AS product_group,

(
SELECT GROUP_CONCAT(hdb.product_reference SEPARATOR ", ") 
    FROM ps_order_detail hdb 
    WHERE hdb.id_order = a.id_order
) AS sku,

(
SELECT GROUP_CONCAT(rop.id_order_lengow SEPARATOR ", ") 
    FROM ps_lengow_orders rop 
    WHERE rop.id_order = a.id_order
) AS marketplace,

( 
SELECT name 
FROM ps_supplier 
WHERE id_supplier = ( 
                        SELECT id_supplier FROM ps_product WHERE id_product = ( 
                            SELECT product_id FROM ps_order_detail rypl 
                            WHERE rypl.id_order = a.id_order 
                            LIMIT 1 
                        ) 
                    ) 
) 
AS fournisseur,

lo.id_order_lengow,
IF(lo.marketplace IS NOT NULL, lo.marketplace, 'royalprice') AS marketplace_name,

( 
    SELECT SUM( 
        IF ( lo.marketplace IS NULL AND 4 IN 
            (
                SELECT ps.id_supplier 
                FROM ps_product_supplier ps 
                WHERE p.id_product=ps.id_product
                )
            AND (
                SELECT cpp.price 
                FROM ps_cdiscount_products_price cpp 
                WHERE cpp.sku=p.reference LIMIT 1
                ) IS NOT NULL, 
            ( 
                SELECT cpp.price 
                FROM ps_cdiscount_products_price cpp 
                WHERE cpp.sku=p.reference LIMIT 1 
                ), 
            p.wholesale_price 
            ) 
        ) 
    FROM ps_product p
    WHERE p.id_product IN (
                            SELECT od.product_id AS id_product 
                            FROM ps_order_detail od 
                            WHERE od.id_order = a.id_order 
                            ) 
) AS total_wolesale, 

(
    SELECT mrp.margin_value 
    FROM ps_margin_rp mrp 
    WHERE mrp.id_order=a.id_order 
    LIMIT 1 
) AS margin_rp_value , 

(
    SELECT mmax.margin_value 
    FROM ps_margin_max mmax 
    WHERE mmax.id_order=a.id_order 
    LIMIT 1 
) AS margin_max_value , 
address.postcode 
FROM ps_orders a 

LEFT JOIN ps_customer c ON (c.id_customer = a.id_customer) 
INNER JOIN ps_address address ON address.id_address = a.id_address_delivery 
INNER JOIN ps_country country ON address.id_country = country.id_country 
INNER JOIN ps_country_lang country_lang ON (country.id_country = country_lang.id_country AND country_lang.id_lang = 1) 
LEFT JOIN ps_order_state os ON (os.id_order_state = a.current_state) 
LEFT JOIN ps_order_state_lang osl ON (os.id_order_state = osl.id_order_state AND osl.id_lang = 1) 
LEFT JOIN ps_lengow_orders lo ON (lo.id_order = a.id_order) 
WHERE 1 AND lo.marketplace IN ("Amazon","Ebay_fr") 
ORDER BY a.id_order DESC LIMIT 0,300

我不明白为什么要花这么长时间。我怎样才能让它更快？
注：：@remcok，我跑解释，我得到解释跑所有！索引照片