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