MySQL PRIMARY Index Not Used – Troubleshooting Low Entry Counts

indexMySQLmysql-5.7

this query :

EXPLAIN SELECT cmd.cmd_date, liv.liv_nom
FROM cmd
    LEFT JOIN liv USE INDEX(PRIMARY) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC
LIMIT 0, 20;

gives me a different result when the table liv contains 5 or 6 rows.

6 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using filesort
1   SIMPLE  l       eq_ref  PRIMARY PRIMARY 4   cmd.liv_id  1   100 

5 rows :

1   SIMPLE  cmd     ALL                 134331  100 Using temporary; Using filesort
1   SIMPLE  l       ALL PRIMARY             5   100 Using where; Using join buffer (Block Nested Loop)

I've read somewhere (can't find where anymore :/) that MySQL can choose to not use a primary if there is too few rows in a table (here the limit seems to be 5). Is there a way to disable this ? Because with the index the query takes 80ms, vs 4s without index :/

I'm using MySQL 5.7.9

Thanks


Here the 2 tables, simplified.

EDIT :

CREATE TABLE `liv` (
  `liv_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `liv_nom` varchar(100) NOT NULL,
  `liv_denomination` varchar(100) NOT NULL,
  `liv_public` tinyint(1) unsigned NOT NULL,
  `liv_slug` varchar(20) NOT NULL,
  `liv_ordre` tinyint(2) unsigned NOT NULL DEFAULT '99',
  `liv_id_crm` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`liv_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

create table cmd
(
    cmd_id int(10) unsigned auto_increment
        primary key,
    str_id int(10) unsigned null,
    con_id int(10) unsigned null,
    pri_id int(10) unsigned not null,
    liv_id int(10) unsigned null,
    ... 20+ more columns with data only ...
    cmd_transaction_id int(10) unsigned null,
    constraint fk_bout_commande_bout_structure1
        foreign key (str_id) references structure (str_id)
            on update cascade,
    constraint fk_crm_commande_crm_contact1
        foreign key (con_id) references contact (con_id)
            on update cascade,
    constraint fk_crm_commande_livre_campagne1
        foreign key (pri_id) references prix (pri_id)
            on update cascade,
    constraint fk_crm_commande_famille_livreur1
        foreign key (liv_id) references liv (liv_id)
            on update cascade,
);

create index fk_bout_commande_bout_structure1_idx
    on crm_commande (str_id);
create index fk_crm_commande_crm_contact1_idx
    on crm_commande (con_id);
create index fk_crm_commande_famille_livreur1_idx
    on crm_commande (liv_id);

Best Answer

It seems you can force MySQL to use an index with force index, documented here.

First, make sure your statistics are up to date.

Then try something like this (untested)

SELECT cmd.cmd_date, liv.liv_nom
FROM cmd 
    LEFT JOIN liv force index (primary) ON cmd.liv_id = liv.liv_id
WHERE 1
ORDER BY cmd_date DESC

Different index hints might work better. Expect different behavior when liv contains only 5 or 6 rows total rather than 5 or 6 matching rows out of thousands or millions of rows.