MySql query not using indexes set

Question

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.

The query structure is simple:

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

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.

I tried to set the MySql variable 'max_seeks_for_key' to 1, but, it is still not using indexes.

I think that I'm missing something basic, but I can't figure it out what.

EXPLAIN for this query is:

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

Any ideea is apreciated.

Answer 1

I didn't found any solution and I just used... FORCE INDEX (status).

Answer 2

This should be more efficient than even with the FORCE:

SELECT  ...
    FROM ( SELECT  id
              FROM  client
              WHERE  status != 5
              ORDER BY  id DESC
              LIMIT  10 OFFSET 10 
          ) AS ids
    JOIN  client USING(id)
    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`)
    ORDER BY  client.id DESC -- Yes, repeated here 

Answer 3

MySQL should be able to use an index on the ORDER BY clause to quickly track the records required from the client table. From there, joining few rows to the other tables to get more information should be quick.

Try to add this index:

ALTER TABLE `client` ADD INDEX `client_idx_id` (`id`);

If MySQL chooses not to use this index (sometimes it makes the wrong decision), try to force it and compare the execution duration of this option.

Answer 4

Whether it is beneficial to use an index for ORDER BY ... LIMIT queries depends on the selectivity of conditions on the table. If most rows does not satisfy the condition, it may be quicker to use a table scan than to scan large parts of the index.

Hence, if the the estimate for the selectivity is off, the query optimizer may choose a non-optimal plan. For this query, if there is no index on the status column, the estimate will be inaccurate since the optimizer will not have any statistics on the distribution of this column. To get more exact estimates, you can try to create an index on this column. Another option to try is to turn off the use of filtering estimates which was introduced in MySQL 5.7. To do that, execute:

SET optimizer_switch='condition_fanout_filter=off'

However, even if the estimate for the selectivity of the condition is correct, the optimizer may fail to choose the optimal plan if the columns are correlated. In your case, I suspect there may be a high correlation between status and id. Maybe there are very few rows where status!=5 overall, but it is pretty frequent among rows with high ids. If that is the case, then I fear that the query optimizer will not be able to detect that, and the only way to get the most optimal plan is to use an index hint.

If you can provide the optimizer trace for this query, I would be able to understand more of what is happening. See https://oysteing.blogspot.no/2016/01/how-to-get-optimizer-trace-for-query.html for advice on how to get the optimizer trace.