MySQL - slow query optimisation

I have a problem optimizing a really slow prestashop SQL query (mysql takes more than 2 seconds to get the result).

Query

SELECT SQL_CALC_FOUND_ROWS a.`id_order`,
                           `reference`,
                           `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`,
                           carrier.`name`                                     AS `carriername`,

                           country_lang.name                                  as cname,
                           IF(a.valid, 1, 0)                                     badge_success,
                           shop.name                                          as shop_name
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` = 2)
         INNER JOIN `ps_carrier` carrier ON (carrier.`id_carrier` = a.`id_carrier`)
         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` = 2)
         LEFT JOIN `ps_shop` shop ON a.`id_shop` = shop.`id_shop`
WHERE 1
  AND a.id_shop IN (1, 5, 3, 4)
ORDER BY a.id_order DESC
LIMIT 0, 50;

The explain result

CREATE TABLE `ps_orders` (
  `id_order` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reference` varchar(9) DEFAULT NULL,
  `id_shop_group` int(11) unsigned NOT NULL DEFAULT '1',
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_carrier` int(10) unsigned NOT NULL,
  `id_lang` int(10) unsigned NOT NULL,
  `id_customer` int(10) unsigned NOT NULL,
  `id_cart` int(10) unsigned NOT NULL,
  `id_currency` int(10) unsigned NOT NULL,
  `id_address_delivery` int(10) unsigned NOT NULL,
  `id_address_invoice` int(10) unsigned NOT NULL,
  `current_state` int(10) unsigned NOT NULL,
  `secure_key` varchar(32) NOT NULL DEFAULT '-1',
  `payment` varchar(255) NOT NULL,
  `conversion_rate` decimal(13,6) NOT NULL DEFAULT '1.000000',
  `module` varchar(255) DEFAULT NULL,
  `recyclable` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `gift` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `gift_message` text,
  `mobile_theme` tinyint(1) NOT NULL DEFAULT '0',
  `shipping_number` varchar(64) DEFAULT NULL,
  `total_discounts` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_discounts_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_discounts_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_paid_real` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_products` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_products_wt` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_shipping_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `carrier_tax_rate` decimal(10,3) NOT NULL DEFAULT '0.000',
  `total_wrapping` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_wrapping_tax_incl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `total_wrapping_tax_excl` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `round_mode` tinyint(1) NOT NULL DEFAULT '2',
  `round_type` tinyint(1) NOT NULL DEFAULT '1',
  `invoice_number` int(10) unsigned NOT NULL DEFAULT '0',
  `delivery_number` int(10) unsigned NOT NULL DEFAULT '0',
  `invoice_date` datetime NOT NULL,
  `delivery_date` datetime NOT NULL,
  `valid` int(1) unsigned NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `client_message` text,
  `ac_processed` tinyint(1) DEFAULT '0',
  `twilio` tinyint(1) DEFAULT NULL,
  `stop_sync` int(1) NOT NULL DEFAULT '0',
  `moloni_internal` tinyint(1) DEFAULT '0',
  `moloni_offers` tinyint(1) DEFAULT '0',
  `bonus_credited_at` datetime DEFAULT NULL,
  `feedback_survey_sent_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id_order`),
  KEY `reference` (`reference`),
  KEY `id_customer` (`id_customer`),
  KEY `id_cart` (`id_cart`),
  KEY `invoice_number` (`invoice_number`),
  KEY `id_carrier` (`id_carrier`),
  KEY `id_lang` (`id_lang`),
  KEY `id_currency` (`id_currency`),
  KEY `id_address_delivery` (`id_address_delivery`),
  KEY `id_address_invoice` (`id_address_invoice`),
  KEY `id_shop_group` (`id_shop_group`),
  KEY `current_state` (`current_state`),
  KEY `id_shop` (`id_shop`),
  KEY `date_add` (`date_add`)
) ENGINE=InnoDB AUTO_INCREMENT=185578 DEFAULT CHARSET=utf8

I found out that if I remove both SQL_CALC_FOUNDS_ROWS and the order by clause the execution time is reduced from 2 seconds to 250ms.

This is a query from Prestashop... not sure if I can remove the SQL_CALC_FOUND_ROWS and what would be the impact.

How can I optimise this query?

Solution

Does this give the correct count?

SELECT COUNT(*) FROM a
    WHERE a.id_shop IN (1, 5, 3, 4)

If yes, then use that instead of SELECT FOUND_ROWS().

Does this give you the rows of a that you want?

SELECT a.id FROM a
    WHERE a.id_shop IN (1, 5, 3, 4)
    ORDER BY a.id_order DESC
    LIMIT 0, 50;

Add these two indexes to a:

INDEX(id_shop, id_order),
INDEX(id_order)

Do the above queries run in a reasonable time?

If yes, the do something like

SELECT ...
    FROM ( the query above, with the ORDER BY and LIMIT )
    JOIN a  USING(id)   -- "self join"
    and the rest of the joins
    ORDER BY a.id_order DESC;

Please provide SHOW CREATE TABLE ps_orders in text, not image.