title: MySql query not using indexes set
body: <p>Recently we switched a db from MariaDB 10.2 to Percona Server (Mysql 5.7) and we have a query that is taking about 15 seconds (before it was about 0.5) because the query optimiser is not using any index on the main table. Because the app logic, we can't change the query format, we need to make the DB to use an index.</p>
  
  <p>The query structure is simple:</p>
  
  <pre><code>EXPLAIN SELECT `clients`.`id` AS `t0_c0`,
         `client`.`name1` AS `t0_c1`,
         `client`.`name2` AS `t0_c2`,
         `users`.`id` AS `t1_c0`,
         `users`.`suffix` AS `t1_c1`,
         `users`.`position` AS `t1_c2`,
         `users`.`first_name` AS `t1_c3`,
         `users`.`last_name` AS `t1_c4`,
         `privateData`.`id` AS `t2_c0`,
         `privateData`.`first_name` AS `t2_c1`,
         `privateData`.`last_name` AS `t2_c2`,
         `tariff`.`id` AS `t3_c0`,
         `tariff`.`provider_id` AS `t3_c1`,
         `tariff`.`tariff_type` AS `t3_c2`,
         `tariff`.`name` AS `t3_c3`,
         `providers`.`id` AS `t4_c0`,
         `providers`.`name1` AS `t4_c1`,
         `providers`.`name2` AS `t4_c2`,
         `addresses`.`id` AS `t5_c0`,
         `addresses`.`zipcode` AS `t5_c1`,
         `addresses`.`country` AS `t5_c2`,
         `addresses`.`city` AS `t5_c3`,
         `private`.`id` AS `t6_c0`,
         `private`.`first_name` AS `t6_c1`,
         `private`.`last_name` AS `t6_c2`,
         `commercial`.`id` AS `t7_c0`,
         `commercial`.`name1` AS `t7_c1`,
         `commercial`.`name2` AS `t7_c2`,
         `commercial`.`name_on_invoice` AS `t7_c3`,
         `commercial`.`organization_type` AS `t7_c4`,
         `organizations`.`id` AS `t8_c0`,
         `organizations`.`person_id` AS `t8_c1`,
         `organizations`.`address_id` AS `t8_c2`,
         `organizations`.`status` AS `t8_c3`,
         `shaddresses`.`id` AS `t9_c0`,
         `shaddresses`.`zipcode` AS `t9_c1`,
         `shaddresses`.`country` AS `t9_c2`,
         `shaddresses`.`city` AS `t9_c3`,
         `shprivate`.`id` AS `t10_c0`,
         `shprivate`.`first_name` AS `t10_c1`,
         `shprivate`.`last_name` AS `t10_c2`,
         `coraddresses`.`id` AS `t11_c0`,
         `coraddresses`.`zipcode` AS `t11_c1`,
         `coraddresses`.`country` AS `t11_c2`,
         `corprivate`.`id` AS `t12_c0`,
         `corprivate`.`first_name` AS `t12_c1`,
         `corprivate`.`last_name` AS `t12_c2`,
  FROM `client` `client`
  LEFT OUTER JOIN `users` `users` ON (`client`.`user_id`=`users`.`id`)
  AND (users.status!=5)
  LEFT OUTER JOIN `private` `privateData` ON (`users`.`person_id`=`privateData`.`id`)
  LEFT OUTER JOIN `tariff` `tariff` ON (`client`.`rate_id`=`tariff`.`id`)
  LEFT OUTER JOIN `providers` `providers` ON (`client`.`provider_id`=`providers`.`id`)
  LEFT OUTER JOIN `addresses` `addresses` ON (`client`.`main_address_id`=`addresses`.`id`)
  LEFT OUTER JOIN `private` `private` ON (`client`.`main_person_id`=`private`.`id`)
  LEFT OUTER JOIN `commercial` `commercial` ON (`client`.`main_organization_id`=`commercial`.`id`)
  LEFT OUTER JOIN `organizations` `organizations` ON (`client`.`id_organization`=`organizations`.`id`)
  AND (organizations.status!=5)
  LEFT OUTER JOIN `addresses` `shaddresses` ON (`client`.`shipping_address_id`=`shaddresses`.`id`)
  LEFT OUTER JOIN `private` `shprivate` ON (`client`.`shipping_person_id`=`shprivate`.`id`)
  LEFT OUTER JOIN `addresses` `coraddresses` ON (`client`.`correspondense_address_id`=`coraddresses`.`id`)
  LEFT OUTER JOIN `private` `corprivate` ON (`client`.`correspondense_person_id`=`corprivate`.`id`)
  WHERE (client.status!=5)
  ORDER BY client.id DESC
  LIMIT 10
  OFFSET 10
  </code></pre>
  
  <p>I can change any INDEX, but, I can't change the query.
  On the old host it was running in 0.2 second, but , the optimiser it was using indexes from clients table. With Percona Server (mysql 5.7) it is taking 15 seconds. Optimiser is not ussing any index from clients table. With FORCE INCEX() from clients table is going under 1 second (with compound index is going in about 0.2 seconds).
  Table 'providers' it has only 1 line.
  I have set indexes on 'clients' table, but, in explain they are not shows as posible keys.</p>
  
  <p>I tried to set the MySql variable 'max_seeks_for_key' to 1, but, it is still not using indexes.</p>
  
  <p>I think that I'm missing something basic, but I can't figure it out what.</p>
  
  <p>EXPLAIN for this query is:</p>
  
  <p><a href="https://i.stack.imgur.com/HoD6s.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HoD6s.jpg" alt="enter image description here"></a></p>
  
  <p>ORDER BY is generating TEMPORARY TABLE and that is ussing all of the resources (without order by is running in under one second, even without INDEX). </p>
  
  <p>Any ideea is apreciated.</p>