I have two tables from django in mysql 5.7, auth_user
and account_account
:
CREATE TABLE `auth_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(150) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`email` varchar(75) NOT NULL,
`password` varchar(128) NOT NULL,
`is_staff` tinyint(1) NOT NULL,
`is_active` tinyint(1) NOT NULL,
`is_superuser` tinyint(1) NOT NULL,
`last_login` datetime DEFAULT NULL,
`date_joined` datetime NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `is_staff` (`is_staff`)
) ENGINE=InnoDB AUTO_INCREMENT=4438 DEFAULT CHARSET=utf8;
CREATE TABLE `account_account` (
`user_ptr_id` int(11) NOT NULL,
`org_form` smallint(5) unsigned DEFAULT NULL,
`org_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`contact_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`balance` decimal(15,2) NOT NULL,
`total_debt` decimal(15,2) NOT NULL,
`contact_email` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`mob_phone` varchar(255) NOT NULL,
`other_phone` varchar(255) NOT NULL,
`delivery_methods` smallint(5) unsigned NOT NULL,
`delivery_address` longtext NOT NULL,
`payment_method` smallint(5) unsigned NOT NULL,
`person_type` smallint(5) unsigned NOT NULL,
`client_type` smallint(5) unsigned NOT NULL,
`city` smallint(5) unsigned NOT NULL DEFAULT '1',
`patronymic` varchar(255) NOT NULL,
`can_delay` tinyint(1) NOT NULL,
`status` smallint(5) unsigned NOT NULL,
`block_reason` varchar(255) NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`manager_id` int(11) DEFAULT NULL,
`wholesale_level_id` int(11) DEFAULT NULL,
`markup_type` smallint(5) unsigned NOT NULL,
`org_markup_level_id` int(11) DEFAULT NULL,
`use_vat` tinyint(1) NOT NULL,
`last_order_date` datetime NOT NULL,
`group_id` int(11) DEFAULT NULL,
`is_partner` tinyint(1) NOT NULL,
`partner_markup` decimal(15,2) DEFAULT NULL,
`call_markup` decimal(15,2) DEFAULT NULL,
`delivery_price` decimal(15,2) DEFAULT NULL,
`city_id` int(11) NOT NULL,
`unloading_at` time NOT NULL,
`site` varchar(200),
`ip_address_id` varchar(20) DEFAULT NULL,
`ip_restriction` tinyint(1) NOT NULL,
`visualisation_id` int(11),
`_express_delivery_price` int(10) unsigned DEFAULT NULL,
`is_express_delivery` tinyint(1) NOT NULL,
`payment_type` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`user_ptr_id`),
KEY `account_account_501a2222` (`manager_id`),
KEY `account_account_7c811b3c` (`wholesale_level_id`),
KEY `group_id_refs_id_35dd1116` (`group_id`),
KEY `city_id_refs_id_96652cf` (`city_id`),
KEY `account_account_ip_address_id_e42ae8c3_uniq` (`ip_address_id`),
KEY `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` (`visualisation_id`),
CONSTRAINT `account_ac_visualisation_id_cd5729f6_fk_account_visualisation_id` FOREIGN KEY (`visualisation_id`) REFERENCES `account_visualisation` (`id`),
CONSTRAINT `account_accou_ip_address_id_e42ae8c3_fk_account_ipadress_address` FOREIGN KEY (`ip_address_id`) REFERENCES `account_ipadress` (`address`),
CONSTRAINT `account_account_city_id_2ab1a62f_fk_classifiers_city_id` FOREIGN KEY (`city_id`) REFERENCES `classifiers_city` (`id`),
CONSTRAINT `group_id_refs_id_35dd1116` FOREIGN KEY (`group_id`) REFERENCES `account_clientgroup` (`id`),
CONSTRAINT `user_ptr_id_refs_id_17b5ed9e` FOREIGN KEY (`user_ptr_id`) REFERENCES `auth_user` (`id`),
CONSTRAINT `wholesale_level_id_refs_id_369bcb86` FOREIGN KEY (`wholesale_level_id`) REFERENCES `classifiers_wholesalelevel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I'm trying to do query:
explain SELECT `auth_user`.`id`, `account_account`.`contact_name`, `account_account`.`contact_email`,
`account_account`.`phone`
FROM `account_account`
INNER JOIN `auth_user` ON (`account_account`.`user_ptr_id` = `auth_user`.`id`)
WHERE (`auth_user`.`is_staff` = 0)
LIMIT 16;
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
| 1 | SIMPLE | account_account | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3741 | 100.00 | NULL |
| 1 | SIMPLE | auth_user | NULL | eq_ref | PRIMARY,is_staff | PRIMARY | 4 | raros.account_account.user_ptr_id | 1 | 100.00 | Using where |
+----+-------------+-----------------+------------+--------+------------------+---------+---------+-----------------------------------+------+----------+-------------+
It shows that account_account does full scan even if we do not do any filtering on account, only join. Can you explain to me two things:
- why mysql does not take
is_staff
index for initial filtering? we have two indexes onauth_user
: primary andis_staff
- why mysql does full scan on account_account, even if we do not do any filtering for
account_account
table?
Thanks in advance!
Best Answer
It may be that it is quite optimal the way you have it.
First note that the
ALL
and3741 Rows
inEXPLAIN
are not necessarily meaningful. This is becauseEXPLAIN
rarely noticesLIMIT
when listing such data.Let's suppose it does do
ALL
:ORDER BY
).auth_user
via thePRIMARY KEY
is quite efficient.is_staff
. If it is usually0
, then all is well. If it is rarely0
, then there is a lot of wasted effort.is_staff
, this required looking at between 16 and 3741 rows.Add an
ORDER BY
; then let's re-Explain it.